Return Value based on MAX Date + Criteria

CJ Dijkstra

Hi All,

I have a database from which I would like to retrieve summary information.

Sample database:

Sheet 2 is my summary sheet in which I need to pull the values based on the MAX date in the database:

Sheet 2:

I would really appreciate some help on possible formula suggestions.

Thanks

Chris

Genevieve P.

Hi @CJ Dijkstra

You'll need to add your two criteria to the MAX portion of your formula as well, otherwise it just looks for the MAX date in the entire sheet instead of the MAX date for that Fleet No.

Ex:

=INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX(COLLECT({Date}, {Fleet No}, [Fleet No]@row))), 1)

If this still gives you an error in your other sheet, check each of the ranges to make sure that there are no #INVALID VALUE errors housed in a cell in any of those referenced columns:

{Value}

{Fleet No}

or

{Date}

If there's a cell with an error in any of these columns, this will cause the formula referencing these columns to error as well.

Let me know if this worked/helped!

Cheers,

Genevieve

CJ Dijkstra

@Paul Newcome Sorry to direct message you on this one but I would really like to have your input on this.

My formula as follows: =INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX({Date})), 1)

This formula works as expected - providing 30, 2320 and 1520.

However, when I am doing the same in a different environment (more complex data set) I get a #INVALID VALUE - now I suspect this is got something to do with the range etc. but I cant get to the bottom of this.

Many Thanks

CJ

Genevieve P.

Hi @CJ Dijkstra

You'll need to add your two criteria to the MAX portion of your formula as well, otherwise it just looks for the MAX date in the entire sheet instead of the MAX date for that Fleet No.

Ex:

=INDEX(COLLECT({Value}, {Fleet No}, [Fleet No]@row, {Date}, MAX(COLLECT({Date}, {Fleet No}, [Fleet No]@row))), 1)

If this still gives you an error in your other sheet, check each of the ranges to make sure that there are no #INVALID VALUE errors housed in a cell in any of those referenced columns:

{Value}

{Fleet No}

or

{Date}

If there's a cell with an error in any of these columns, this will cause the formula referencing these columns to error as well.

Let me know if this worked/helped!

Cheers,

Genevieve

CJ Dijkstra

Hi @Genevieve P.

Thank you very much - it makes 100% sense and this solved my problem!! I really appreciate your assistance with this.

Kind Regards

Chris

Genevieve P.

Wonderful! I'm glad I could help 🙂