Forum Discussion

Woody36060's avatar
Woody36060
Copper Contributor
Oct 27, 2023
Solved

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. ...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Oct 28, 2023

    Woody36060 

    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.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 27, 2023

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!

Resources