asked on

Excel Vlookup using max

Hi, I am trying to write logic in Excel to look at a column of value based on another value and find the max value in a third column. I don't know how to incorporate the max function into Vlookup or maybe I should be using a different formula?

Here's my current formula:

=IF(ISNA(VLOOKUP(N2,$L$1:$M$500,2,FALSE)), "Not Found", VLOOKUP(N2,$L$1:$M$500,2,FALSE))

Open in new window

Here's an example of data below. My current formula returns what's in column O but I want the results to return the values in P (I hard-coded these values for this example). The value in cell P2 should be 4 because, of the 3 rows with Section "Material Characterization" under col L, the number 4 is the highest value in col M.

For each of the 6 section name values (col N), I want to find the highest Hidden Residual Risk # value from col M where the section in L matches the section in N.

I appreciate any help you can offer. Thanks, Alexis

HainKurt

can you post a sample excel as well...

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Or you may also try this...

=IF(ISNA(MATCH(N2,$L$2:$L$500,0)),"Not Found",MAX(INDEX(($L$2:$L$500=N2)*($M$2:$M$500),)))

Open in new window

and copy it down.

HainKurt

try

=MAX(IF(L2:L10=N2, M2:M10))

Ctrl+Shift+ENTER

alexisbr

ASKER

Thanks, byundt! That's just what I needed. I was not familiar with the AGGREGATE function in Excel 2010 and should have mentioned what version of Excel I am using. Thanks again!

I didn't test the other solutions that came after the first one but thank you all. I spent hours trying to figure this out and just could not get the results I needed.

Regards,
Alexis