# 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

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1