# Need help understanding "Index" and "Match"

1. ## Need help understanding "Index" and "Match"

Hello All

I am not sure if it is appropriate to ask for helping understand how to use Excel functions.

I was given this formula to help create a unique list....this formula below provides the unqiue data for column K. The column is changed to get the unique data for that column.

=IFERROR(
INDEX(\$C\$5:\$X\$241,
MATCH(ROW(BX1), \$J\$5:\$J\$241,0),
MATCH(\$K\$5, \$C\$5:\$X\$5,0)),"")

It works.... but I dont understand how. I am having trouble with another issue (I have it posted it here on the site that hasnt been solved yet) and I am hoping to leverage this to hopefully resolve my other problem.

This is what I understand...
INDEX(\$C\$5:\$X\$241 is setting the area within the excel document where the forumla is to be applied.
BX1 is just a way to keep track of the rows.
0 is saying it needs to be an exact match.
\$J\$5:\$J\$241 contains a running total in my data that is a running total of a unique entry (so if there were 100 data entries, but only 5 were unique, that column would have the numbers 1-5).

I don't understand...
MATCH(\$K\$5, \$C\$5:\$X\$5
Why is the first part only referring to one cell, specifically the \$K\$5?

In my data the column K has a lot of duplicates, but it appears that only the very first cell is used for comparison. So I don't understand how it only looks at one cell....but returns a unique values for the entire column.

How is that getting the correct information? I mean it works... but i am missing the logic.

Joanne  Register To Reply

2. ## Re: Need help understanding "Index" and "Match"

You have INDEX ... MATCH1 ...MATCH2

The MATCH1 expression tells Excel which row within the table that is being indexed (i.e. C5:X241) the data should be returned from, and the MATCH2 expression defines the column where the data should be returned from. Note that MATCH2 is finding a match for K5 within the row range of C5:X5 - it will always match with K5, which is the 9th column of that range, so that is the column where it will get the data from.

Hope this helps.

Pete  Register To Reply

3. ## Re: Need help understanding "Index" and "Match"

=MATCH(look for what, look for it where, 0 for exact match)

=INDEX(using what table, pull reference from row #, pull reference from column #)

Assuming A1:A10 are 10-1 (in reverse), then MATCH(3,A1:A10,0) = 8 because the 3 would be in A8, the 8th row down.

=INDEX(A1:C10,3,3) would pull the value of C10, the 3rd row and 3rd column in.

=IFERROR(

INDEX(\$C\$5:\$X\$241, = the table
MATCH(ROW(BX1), \$J\$5:\$J\$241,0), match the row # to the value in J5:J241 = Let's pretend this = 10
MATCH(\$K\$5, \$C\$5:\$X\$5,0)), match whatever is in K5 in C5:X5 = Let's pretend this = 3

"")

With the above values, =INDEX(\$C\$5:\$X\$241,MATCH(ROW(BX1), \$J\$5:\$J\$241,0),MATCH(\$K\$5, \$C\$5:\$X\$5,0)) = the value of E14. It's 10 rows down from C5 and 3 columns in.

Here's an example if you need a visual reference.

index - match for beginners.xlsx  Register To Reply