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.

     

Woody36060's avatar
Woody36060
Copper Contributor
Oct 27, 2023

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

 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 27, 2023

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.

Resources