Forum Discussion
Need help with a Lookup Formula
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 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.
- 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.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 ) )
