Forum Discussion
Need help with a Lookup Formula
I need to create a formula to associate a (Text) in Column A with the largest number(Value) in a designated column to the right (1-5) and return the associated text value to a cell in REOCURRANCE.
PeterBartholomew1 has already answered your question regarding the spill error. Thank you PeterBartholomew1 .
=INDEX($A$3:$A$28,MATCH(1,(B$3:B$28=LARGE(B$3:B$28,ROW($H1)))*(COUNTIF(I$1:I1,$A$3:$A$28)=0),0))An alternative with older versions of Excel could be this formula. The formula is in cell I2 and filled across range I2:M5. The formula must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021. The entries in cells H2:H5 are done manually in this example.
10 Replies
A REDUCE variant:
=LET( counter, SEQUENCE(COLUMNS(matrix)), REDUCE( {"#1"; "#2"; "#3"; "#4"}, counter, LAMBDA(a, v, LET( by_array, INDEX(matrix, , v), sorted, SORTBY(letter, by_array, -1), top_4, TAKE(sorted, 4), HSTACK(a, top_4) ) ) ) )Variants with 365/Web functions:
=LET( x, 4, JoinLargest, LAMBDA(col, TEXTJOIN(";",,TAKE(SORTBY(Table[LETTER],col,-1),x))), HSTACK( "#" & SEQUENCE(x), WRAPCOLS(TEXTSPLIT(TEXTJOIN(";",,BYCOL(Table[[1]:[5]],JoinLargest)),,";"),x) ) )=LET( x, 4, GetLetter, LAMBDA(rw,cl, INDEX(SORTBY(Table[LETTER], CHOOSECOLS(Table,cl+1), -1),rw) ), HSTACK( "#" & SEQUENCE(x), MAKEARRAY(x,5, GetLetter) ) )Just to demonstrate that the Thunk solution is not a figment of the imagination!
= LET( vectorsϑ, BYCOL(table, LAMBDA(column, LET( vector, TAKE(SORTBY(letter,column,-1),4), LAMBDA(vector) ) )), MAKEARRAY(4, 5, LAMBDA(r,c, INDEX(INDEX(vectorsϑ,1,c)(), r,1) )) )The first lines are the solution but Excel will moan about nested arrays despite the fact that the whole purpose of the formula is to deliver a nested array as the correct solution to the problem. Instead, the further LAMBDA is used to turn each inner array into a function that takes a null parameter string in order to return the array (a Thunk). The second part of the formula takes the Thunk array, element by element, opening it and selecting a single value, before returning control to MAKEARRAY. What a pain!
=REDUCE({"1 #","2 #","3 #","4 #"},SEQUENCE(,5),LAMBDA(x,y,HSTACK(x,
TAKE(SORT(HSTACK(A2:A27,CHOOSECOLS(B2:F27,y)),2,-1),4,1))))With Office 365 or Excel for the web you can use this formula.
PeterBartholomew1 has already answered your question regarding the spill error. Thank you PeterBartholomew1 .
=INDEX($A$3:$A$28,MATCH(1,(B$3:B$28=LARGE(B$3:B$28,ROW($H1)))*(COUNTIF(I$1:I1,$A$3:$A$28)=0),0))An alternative with older versions of Excel could be this formula. The formula is in cell I2 and filled across range I2:M5. The formula must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021. The entries in cells H2:H5 are done manually in this example.
Nice! I am beginning to get so fed up with building workarounds for Microsoft's monumental blunder in specifying how nested arrays should be treated.
In the present case, I had written a core formula
= SORTBY(letter, column,-1), 4)but hadn't made up my mind whether to go the REDUCE/HSTACK route or MAKEARRAY. At least I could avoid expanding Thunks! I hate trying to explain those to a bewildered user. Creating an array of arrays should be no more complicated that creating the array of concatenated strings
= BYCOL(table, LAMBDA(column, CONCAT(TAKE(SORTBY(letter, column,-1), 4)) ) )For fixed length strings one could then use
= LET( result, BYCOL(table, LAMBDA(column, CONCAT(TAKE(SORTBY(letter,column,-1),4)))), MID(result, {1;2;3;4},1) )since MID will return nested arrays.
It is not as if this is a marginal requirement; the majority of workbooks I write require arrays of arrays in more than a single formula. The decision may have retained backward compatibility in Excel, but at the expense of creating an utter mess going forward!
