Need help with a Lookup Formula

[Woody36060]

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.

[#2]

@Woody36060 

=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.

[#3]

@OliverScheurich 

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!

[#4]

@Woody36060 

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!

[#5]

@OliverScheurich 
Upon inserting your formula into cell H2:H5 or I2:I5 I get the result #Spill.

[#6]

@Woody36060 

These formulas are not entered into multi-cell ranges.  They are entered into a single cell and the array then spills into adjacent cells for presentation purposes.  The spreadsheet I posted contains @OliverScheurich 's solution under the description REDUCE/HSTACK.

[#7]

@Woody36060 

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)
  )
)

[#8]

@Woody36060 

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)
            )
        )
    )
)

[#9 · Accepted solution]

@Woody36060 

@Peter Bartholomew has already answered your question regarding the spill error. Thank you @Peter Bartholomew .

=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]

Thank you Oliver,
I am most familiar with this old style of formulas and will use it. I appreciate all of the help offered by everyone. This reminds me of a story. I was asked "are you a golfer?" I said I thought I was until I played with some real golfers. That's how I feel with you guys. Keep up the good work and know you ARE appreciated.

[#11]

With Excel versions < 2021 the following shouldn't require Ctrl+Shift+Enter

=INDEX($B$3:$B$28,
  AGGREGATE(15,6,
    ROW(B$3:B$28)-ROW(B$2) / (ISNA(MATCH($B$3:$B$28,J$2:J2,0)) * (C$3:C$28=LARGE(C$3:C$28,ROWS($10:10)))),
    1
  )
)