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.

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 28, 2023

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

 

Resources