How to return the maximum value in a multiple results LOOKUP

[martinpe]

What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.

Please see attachment.

Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)

Thanks in advance

[psumvp]

Assuming column A is sorted such that all products are grouped together, you can use an offset function to create a range of all the values in P that correspond to that product in A:

=MAX(OFFSET($P$1,MATCH(T2,A:A,0),0,COUNTIF(A:A,T2),1))

[martinpe]

Works really well expect for the ocassional cell...

If you apply it to the example again and drag it the whole way down it doesn't find the las lookup.


us this:

Please Login or Register  to view this content.

[martinpe]

sorry that is because there is no information underneath it... but on my full spread sheet it is doing the same even with data underneath it.

[psumvp]

sorry I believe I have the offset starting one cell too low, try it as:

=MAX(OFFSET($P$1,MATCH(T2,A:A,0)-1,0,COUNTIF(A:A,T2),1))

It is not working for the last one, because it only has one data item, thus the array is one item in length and presently (incorrectly) the cell below your last line). This will adjust it up one, and make all of the correct.

[martinpe]

take a look at this for example

[martinpe]

Work perfectly thanks mate, That one is solved...