Reputation: 4879
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
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.
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.
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.
Upvotes: 2