IFERROR VLOOKUP MAX value

ItsSugarBunny · Jul 31st 2018

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!

davesexcel · Jul 31st 2018

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

ItsSugarBunny · Jul 31st 2018

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.

davesexcel · Jul 31st 2018

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?

davesexcel · Jul 31st 2018

If all else fails, you can supply a sample workbook.

ItsSugarBunny · Aug 9th 2018

here is i attached the sample workbook. thanks a lot

**AliGW** · Aug 9th 2018

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.

ItsSugarBunny · Aug 9th 2018

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

**AliGW** · Aug 9th 2018

You're welcome!

IFERROR VLOOKUP MAX value

Participate now!

Share