+ Reply to Thread
Results 1 to 3 of 3

Need help understanding "Index" and "Match"

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    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.

    thanks for your help
    Joanne
    Last edited by joannelittell; 10-29-2013 at 02:41 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    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. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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
    Last edited by daffodil11; 10-29-2013 at 03:06 PM. Reason: example

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help understanding "Index" and "Match"
    By joannelittell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2013, 03:08 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  4. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM
  5. Replies: 3
    Last Post: 02-16-2011, 02:55 PM

Bookmarks

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