# Index Match Function

1. ## 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

2. ## Re: Index Match Function

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

3. ## Re: Index Match Function

Hi again,

same outputs using

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

Regards

4. ## Re: Index Match Function

Hi,
Thank you for the reply. The first result should be 225 and the second result in this instance should be 57. I don't want to return any 0
values.

Kind regards,
Tim

5. ## Re: Index Match Function

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.

6. ## Re: Index Match Function

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).

7. ## Re: Index Match Function

Thanks newdoverman,
Is it not possible to say something like if the index match formulae evaluates to 0 then do this

``Please Login or Register  to view this content.``
otherwise do
``Please Login or Register  to view this content.``
??...

I know you can say true or false but this is neither just a null value.....mmmm

Kind regards,
Tim

8. ## Re: Index Match Function

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

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)