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.

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 28, 2023

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

Resources