Vlookup with Max Return formula

**hydraonstimpac** · 02-08-2012, 01:20 PM

I have a table

Column A Column B
A 3
B 7
C 2
A 8
B 9
A 11

Then I have a cell D1 to place any letter... A,B,C... whatever... and then i want a cell to return the MAX number from column B corresponding to what letter i put in D1... and since there are multiple A's, i only want the MAX... so if D1 says A, i need a cell to say 11 since that is the MAX one corresponding to A. I cannot figure out how to set up the formula!

**Andrew-R** · 02-08-2012, 01:24 PM

Try this:

=MAX(INDEX((A1:A6=D1)*B1:B6,0))

**hydraonstimpac** · 02-08-2012, 01:27 PM

that worked! but is there a way to do it without the INDEX function?

**Pete_UK** · 02-08-2012, 01:29 PM

Put this array* formula in D1:

=MAX(IF(A$1:A$10="A",B$1:B$10))

You might need to adjust the ranges to suit your real data. Or you can put A in another cell, eg C1, and the formula then becomes:

=MAX(IF(A$1:A$10=C1,B$1:B$10))

which can be copied down.

* An array formula must be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to edit the formula subsequently, you will need to use CSE again.

Hope this helps.

Pete

**hydraonstimpac** · 02-08-2012, 01:34 PM

that worked Pete! thanks! though again is there anyway to do this without the array?

**Andrew-R** · 02-08-2012, 01:36 PM

If you don't want to use an array function or INDEX the you'd really need a third 'helper' column (say column C) with a formula like:

=IF(A1=$D$1,B1,0)

You could then just use =MAX(C1:C6)

Out of interest, do you have a particular reason for not wanting to use INDEX?

**hydraonstimpac** · 02-09-2012, 08:08 PM

its an assignment for school and the professor said "only functions we have used in class" and we didnt use index... or array

**Pete_UK** · 02-09-2012, 08:23 PM

Well, how are we to know which functions you have actually covered in class?

Pete

**hydraonstimpac** · 02-09-2012, 08:54 PM

you dont know... which is why i asked if u could do it another way. basically it has to be done with IF, Vlookup, or MAX... or other basic excel functions.

**Pete_UK** · 02-09-2012, 09:02 PM

Well, my solution did only include MAX and IF, and if you consider VLOOKUP as a basic Excel function then INDEX should also be classed as such.

Pete

**Andrew-R** · 02-10-2012, 01:48 AM

I'm not quite sure what your professor is after - the lookup functions in Excel (VLOOKUP, HLOOKUP, LOOKUP and MATCH) are designed to return a single result, not a range of results that you can apply MAX to. If you want to use MAX conditionally then you either need an array of values or you need a helper column, and with a helper column any use of a lookup function would be redundant.