Index Match Function

Hi,
I'm trying to use index match to return a value from an index I have setup. It only returns results for half the index because it is also returning the blanks. How do I index the table to return the numeric values?

Regards,
Tim

Hi

in AA9 and down


=INDEX($C$3:$V$22,MATCH(Y9,$B$3:$B$22,0),MATCH(Z9,$C$2:$V$2,0))

First score is zero

Hope it helps

Hi again,

same outputs using



=SUMPRODUCT($C$3:$V$22,($B$3:$B$22=Y9)*($C$2:$V$2=Z9))


Regards

The problem, essentially, is that you're trying to run a lookup on a lower triangular matrix, but you're treating it like a square matrix.

Easiest solution: situationally mirror the lookup across the diagonal of the matrix using MAX and MIN. (Note: This requires that the two headers are in the same order.)

try this:

Please Login or Register  to view this content.

You can nest the MATCHes if you want, I left them in helper cells for fewer operations + easier reading.

The problem is that you need to match the ROW first then match the COLUMN. The original chart only allows for Row first and column Second. Unfortunately, this arrangement eliminates half of the possibilities (the area in green).

To cover the possibility of 0 enter this in AA9 and copy down

Formula:

Please Login or Register  to view this content.