+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Match Syntax - Verify My Understanding

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    18

    Match Syntax - Verify My Understanding

    I'm working with a spreadsheet that uses Index and Match. After reading help and searching this forum I understand Index tells the cell to go to a data range and return the contents of row x and column y. If you put Match in for the column_num the formula looks for a match and finds the relative row number. In my spreadsheet it's looking for an exact match.


    My question is with the last item in the syntax for Index. In cell C12 the last item in the syntax for the Index formula is +1:
    =INDEX($R12:$FM12,MATCH(B$10,$R$10:$FM$10,0)+1)

    Does this mean find the match and go over +1 columns?

    Sample workbook attached.

    Catherine
    Catherine

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Match Syntax - Verify My Understanding

    I think that someone here will be able to give a more textbook explanation, but here is how I interpret it:

    1) In your =INDEX() formula the MATcH() part is actually placed where you would put the row number.
    1.1) So when you add 1, then you are moving down 1 row. But:
    2) Since your matrix from where you want to return a value is 1-dimensional, Excel can figure out that it should be interpreted as a column number
    2.1) So when you add 1, then you are moving 1 column over.
    3) If you change $R12:$FM12 to $R12:$FM13, you wil get a ref error because now the MATCH() value is actually interpreted as a row number, and you will have a lot of rows not contained within $R12:$FM13.
    4) Also you can add an extra , in your formula -> =INDEX($R12:$FM12,,MATCH(B$10,$R$10:$FM$10,0)+1) and still have the same results.
    5) If you change $R12:$FM12 to $R12:$FM13, you can have the same results if you change your formula to =INDEX($R12:$FM13,1,MATCH(B$10,$R$10:$FM$10,0)+1)

    I hope this make sense...
    Sincerely
    S?ren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Registered User
    Join Date
    04-16-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Match Syntax - Verify My Understanding

    Thanks Soren, it does make sense.

    1) I learned that today.
    1.1) But is what I wondered
    3) That's what I guessed and I appreciate you verified it.

    I'll leave it alone; I prefer leaving things "as is" when I inherit a spreadsheet.

    Catherine

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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