Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# formula for a lookup based on the max value of a looked-up range

Posted on 2013-01-10
Medium Priority
260 Views

get the max value in a column based on a code, then do a lookup for value of another column

example attached

thank you very much
example.xlsx
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Assisted Solution

NBVC earned 400 total points
ID: 38764144
Try:

``````=INDEX(Table92[ResultDate],MATCH(G6&"_"&MAX(IF(Table92[code]=G6,Table92[LookupDate])),Table92[code]&"_"&Table92[LookupDate],0))
``````

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down
0

LVL 8

Accepted Solution

ragnarok89 earned 1200 total points
ID: 38764164
This should do it.
example.xlsx
0

LVL 93

Assisted Solution

Patrick Matthews earned 400 total points
ID: 38764183
The following seems to work for me in H6:

=INDEX(A:A,MIN(IF((Table92[ccode]=G6)*(MAX(IF(Table92[ccode]=G6,Table92[LookupDate],""))=Table92[LookupDate]),ROW(Table92[ccode]),10^99)),1)

Note that ccode = code
0

Author Closing Comment

ID: 38767292
Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.