Excel: Index Match array search by ranked customer

user_7168376 0
Viewed 112

Data Sample

I am trying to get the Customer name from ColumnA based on two criteria's. First the Ranking Year, second the criteria. I tried this formula:

INDEX(A3:A10,MATCH(TRUE,INDEX(((F3:H10)=M4)+((F2:H2)=M3)>0,0,1),0))---->Its not picking up exact rankings

I also tried simpler ones:

INDEX(A3:A10,MATCH(1,(M4=F3:H10)*(M3=F2:H2),0)) ---> Didn't work, N/A Result

To make it more clear, i want a function that lookup the ranking years and then the rank number and get me the customer name from columnA. It must be an exact match.

If you wonder why i have ranking is to get the top gainers and losing customer...etc.

Thank you in advance.

array-formulas match indexing excel arrays
1 Answers

Try:

=INDEX($A$3:$A$10,MATCH(M4,INDEX($F$3:$H$10,0,MATCH(M3,$F$2:$H$2,0)),0))
  • The interior index/match returns the appropriate year column for the rank
    • Note that 0 for the row index will return the entire column
  • The exterior index/match returns the name

In older versions of Excel, you may need to confirm this as an array formula. To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

user_28729220
Related