# Index Match Match - syntax problem

1. ## 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

2. ## Re: Index Match Match - syntax problem

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

3. ## 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. ## 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. ## 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. ## 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. ## Re: Index Match Match - syntax problem

Originally Posted by AlanY
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. ## 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. ## Re: Index Match Match - syntax problem

Originally Posted by Dabooka
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. ## Re: Index Match Match - syntax problem

Originally Posted by AlanY
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!

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