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

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

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

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.

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.

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!

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))))

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.

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

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!