# IFERROR VLOOKUP MAX value

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

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

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

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

the result would be 11:
SHEET2
-----
A B
1 11

And i currently use this formula in destination cells:
=IFERROR(VLOOKUP(A2;sheet1!\$A\$1:\$B\$1000053;2;);"")
i use" IFERROR" to turn "#VALUE!" into blank cell
thanks all!

• Code
``=MAX(IF(Sheet1!A1:A14=A1,Sheet1!B1:B14))``

It may have to be an array formula but it did not have to when I was testing it out.
Array formulas need to be confirmed with Ctrl & Shift & Enter

• your formula doesnt work in my workbook is it possible using my current formula? i just have no idea where im suppose to put "max" in my current formula.

• Probably not, vlookup will only find the first instance of "1". I wouldn't give you a formula if I didn't try myself, why doesn't the formula work for you, did you enter it according to your region?

• here is i attached the sample workbook. thanks a lot

## Files

• The formula is fine:

=MAX(IF(Sheet1!\$A\$1:A14=A1,Sheet1!\$B\$1:B14))

However, you have not entered it correctly.

With your cursor in cell B1 where the formula is, confirm by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Then you can copy down.

## Files

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• OMG sorryyy im such a noob Thankyou so muchhhh :')

• You're welcome!

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!