VLookup to return MAX value from multiple hits

[crackod93]

Hi, can anyone help me with the below...

Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.

Lookup number 1 from column A, then give me the largest number from column B

example table:
A B
-----
1 5
2 2
1 11
3 2
4 5

the result would be 11

thanks all!

 

[Weaver]

Have you used Ctrl+Shift+enter to confirm the formula? You'd see curly brackets {} around it (don't type these).
Also, I've not checked this out but it might be that the formula doesn't work if the ranges are in other (possibly closed) workbooks.

 

[Akmialra]

Hello

Try:

=MAX(IF(A1:A100=1,B1:B100))

Commit the formula with CONTROL+SHIFT+ENTER (not just ENTER). If correcly committed the formula will be surrounded with braces {}.

Also, do not use whole columns , e.g. A:A and B:B.

That was really helpful but I'm curious why excel requires the braces to make the formula work, Why it just work that way, I'm want to know the actual reason.

 

[dreid1011]

That was really helpful but I'm curious why excel requires the braces to make the formula work, Why it just work that way, I'm want to know the actual reason.

The { } indicate that it is an array formula. The IF() function naturally only works with single cell references. To make it work with multi-cell references, it must be entered as an array formula. Excel adds the {} to show that you have entered an array formula correctly.

Array formulas are entered by pressing CTRL+SHIFT+ENTER at the same time.

 

[tranquill1800]

I think life is more simple than this....Though Microsoft named excel as "sheet/****", it's mostly garbage in=garbage out...

Probably a quick sorting by the max number and then doing a vlookup will lead to a faster result since vlookup only takes the first top row result..
Hope it helps