+ Reply to Thread
Results 1 to 10 of 10

Index Match Match - syntax problem

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Question Index Match Match - syntax problem

    Hi everyone,

    I'm trying to perform a lookup where the contents of two cells are compared to return a value. I'm fairly sure Index-match-match is the way to go on this, having done something previously (but on a spreadsheet I don't have access to copy my formula from!). I need to see where I'm going wrong, Index arrays always cause me headaches as I forever get the syntax confused; I don't use them often enough to remember.

    I've attached a simplified version of what I need with the expected results entered beneath in red.

    Any help gratefully received, especially if you can tell me the mistake I made too to improve my understanding. Likewise if there's a better and more robust method, I'm open to that too

    Dabooka
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-28-2004
    MS-Off Ver
    2010
    Posts
    225

    Re: Index Match Match - syntax problem

    Enter the formula as an array....see attached
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    Re: Index Match Match - syntax problem

    try this on

    {=IF(A2=0,"",(INDEX($T$3:$T$16,MATCH(A2&B2,$Q$3:$Q$16&$R$3:$R$16,0))))}
    on N2

    and
    {=IF(A2=0,"",(INDEX($U$3:$U$16,MATCH(A2&B2,$Q$3:$Q$16&$R$3:$R$16,0))))}
    on O2

  4. #4
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Index Match Match - syntax problem

    Wow, that's awesome thank you!

    I had been entering it as an array formula but it was still broken; you're formula looks NOTHING like what I was expecting though, much tighter and neater.

    So is your approach just a better way of writing what I wanted, or was I off completely off and it would never have worked? Just curious for the future, as I could have sworn I used something akin to mine in the past.

    Anyway, thanks again.

  5. #5
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    Re: Index Match Match - syntax problem

    you're welcome

    your original formula has 2 Match functions, the first gives the row to index but the second Match return the column ref to the index thus the error.

  6. #6
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Index Match Match - syntax problem

    Thanks Alan,

    Does the $ ensure the cell remains the sdame when dragging and copying?

    At some point in the future I need to find a way to automate the copying of formulas to another row when adding a new record, but that;s for another day!

  7. #7
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Index Match Match - syntax problem

    Quote Originally Posted by AlanY View Post
    you're welcome

    your original formula has 2 Match functions, the first gives the row to index but the second Match return the column ref to the index thus the error.
    Hate to be a pain, but can you spell that out as if I was, er, really bad at this?! Just so i know for next time.

    =IF(A2=0,"",(INDEX(T3:T16,MATCH(A2,Q3:Q16,0),MATCH(B2,R3:R16,0))))

  8. #8
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    Re: Index Match Match - syntax problem

    the index function required row/column reference for it to locate the matched result.

    your first MATCH(A2,Q3:Q16,0) return the row reference which is correct, but
    the second MATCH(B2,R3:R16,0) will return the column reference which is not what you're after.

  9. #9
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    Re: Index Match Match - syntax problem

    Quote Originally Posted by Dabooka View Post
    Thanks Alan,

    Does the $ ensure the cell remains the sdame when dragging and copying?

    At some point in the future I need to find a way to automate the copying of formulas to another row when adding a new record, but that;s for another day!
    yes, it does

  10. #10
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Index Match Match - syntax problem

    Quote Originally Posted by AlanY View Post
    the index function required row/column reference for it to locate the matched result.

    your first MATCH(A2,Q3:Q16,0) return the row reference whick is correct, but
    the second MATCH(B2,R3:R16,0) will return the column reference which is not what you're after.
    Ah, so Match-Match automatically goes Match#1 [Row] Match #2 [Column]? If my undertsanding is right, that probably explains why it worked on my previous work.

    Thanks for that!

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. VBA syntax for Index/Match function
    By SandPounder1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2014, 03:42 PM
  3. [SOLVED] Index Match Match problem
    By nordmann in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-06-2014, 07:44 PM
  4. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  5. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 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