# How to return the maximum value in a multiple results LOOKUP

1. ## How to return the maximum value in a multiple results LOOKUP

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

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)

2. 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))

3. 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.``

4. 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.

5. 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.

6. take a look at this for example

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1