Forum Discussion

dmann1's avatar
dmann1
Copper Contributor
Dec 04, 2025
Solved

Index and Match command is return results from the wrong column

Within Excel I'm using the Index and Match command to search an array. I've provided copy of the workbook.

Here is the command =IFERROR(INDEX(D7:H11,MATCH($D$13,L7:L11,0),MATCH($E$13,M7:M11,0)),"")

Here is an added twist on sheet 1 I use this formula and it works correctly. This formula is on sheet 2. The results for the Average column returns correctly.

The other columns appear as if the were flipped. When the Marginal column is selected I get results from the Above Avg column. The opposite happens if I choose the Above Avg column I get the Marginal results. The same thing happens if I choose the columns on the ends. As shown in the image above the Extreme row is selected and the One column is chosen. The result should be "Caution", but instead the result is "None6".

FYI, this is part of larger workbook where the results will be posted on a different worksheet, but to simplify everything I've placed it on its own page.

Regards

 

  •  

    Hi,

     

    Try this formula in cell G13:

     

    =XLOOKUP(TRUE, C7:C11 = D13, D7:H11) XLOOKUP(TRUE, D6:H6 = E13, D7:H11)

     

    or this formula:

     

    =INDEX(D7:H11, MATCH(D13, C7:C11, 0), MATCH(E13, D6:H6, 0))

     

    Hope this helps.

     

    Regards,

    IlirU

4 Replies

  • IlirU's avatar
    IlirU
    Brass Contributor

     

    Hi,

     

    Try this formula in cell G13:

     

    =XLOOKUP(TRUE, C7:C11 = D13, D7:H11) XLOOKUP(TRUE, D6:H6 = E13, D7:H11)

     

    or this formula:

     

    =INDEX(D7:H11, MATCH(D13, C7:C11, 0), MATCH(E13, D6:H6, 0))

     

    Hope this helps.

     

    Regards,

    IlirU

    • dmann1's avatar
      dmann1
      Copper Contributor

      Thanks, I knew it would seem obvious. Both formulas work.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    to me it looks like the formula is working perfectly.

    M7:M11 is in reversed order hence your reversed outcome

Resources