Mortz
Mortz

Reputation: 4879

How to find the maximum of lookup values in excel

I have an excel table that looks like this

Row    Column1    Column2    Column3
R1     A          B          C 
R2     C          D          X

I have a table that holds the values corresponding to the entries in Columns 1 to 3 which looks like this -

Key    Value
A      1
B      7
C      2
D      4
X      9

I want to create a Column4 that has the maximum looked-up value of columns 1 to 3, i.e. the result would look like this -

Row    Column1    Column2    Column3    Looked_Up_Max
R1     A          B          C          7
R2     C          D          X          9

I tried writing an array formula like this - ={max(if(B1:D1,vlookup(B1:D1,lookup_table!$A$1:$B$5,2,0)))}

But it does not work. Any way to do this is one step instead of say creating three additional columns with the looked up values and then taking a max of the additional columns?

Thank you for the help

Upvotes: 0

Views: 89

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

If the data on the lookup table is sorted then you can use this array formula:

=MAX(LOOKUP(B2:D2,$H$2:$H$5,$I$2:$I$5))

Being an array it needs to be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here


Another that does not care about sort order that uses SUMIFS()

=MAX(SUMIFS(I:I,H:H,B2:D2))

Still an array formula, but this assumes that the Key in the lookup is unique.

enter image description here


If not in order and not unique and the user wants the first then we need this convoluted array formula:

=MAX(IFERROR(INDEX(I:I,N(IF({1},MATCH(B2:D2,H:H,0)))),-1E+99))

Still and Array formula.

enter image description here

Upvotes: 2

Related Questions