Forum Discussion
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
- IlirUBrass 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
- m_tarlerBronze Contributor
to me it looks like the formula is working perfectly.
M7:M11 is in reversed order hence your reversed outcome