CODEWITHSUNDEEP
arraysexcelif-statementmax
ViggieSmalls
ViggieSmalls

Reputation: 73

max if criteria match + vlookup

I currently have the following Max If formula.

{=MAX(IF(A1:A5=A13,H1:H5))}

This would work fine, the only problem is that the h1:h5 values are not numeric. I was thinking of doing a mapping (i.e. a vlookup) of the entries to a numeric value but can't seem to fit in the lookup within the function. I know worst case I can create a separate vlookup formula and find the max based on the lookup of those values, but I'd like to avoid that. Please provide any guidance.

enter image description here

Thanks!

Upvotes: 0

Views: 1079

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use this array formula:

=MAX(IF(A1:A5=A13,LOOKUP(H1:H5,A15:A16,B15:B16)))

Being and array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Upvotes: 1

Related Questions