Index Match on multiple criteria not working

Question

I have two sheets with the following data within;

Sheet1:

Column A (rows 2 - 100): EASTING; Column B (rows 2 - 100): NORTHING; Column C (rows 2 - 100): MYKEY;

Sheet2:

Column A (rows 2 - 100): EASTING; Column B (rows 2 - 100): NORTHING; Column C: MYKEY_TOMATCH

I wish to obtain MYKEY in Sheet2 MYKEY_TOMATCH by matching on EASTING and NORTHING. To do this I am using this formula (entered into cell Sheet2!C2;

=INDEX(Sheet1!C2:100,MATCH(Sheet2!B2,IF(Sheet1!A2:A100=Sheet2!A2,Sheet1!B2:B100,0)))

Entered as an array formula (Ctrl+Shift+Enter).

I have used this formula in the past for this with success. However now it is returning the same "MYKEY" for all rows, namely that for the last row in Sheet1.

What is the error in my formula or my approach?

Answer 1

The index reference is missing a column letter. Also, Match() without the third parameter will default to an approximate match. I guess you're after an exact match instead, so maybe

=INDEX(Sheet1!C2:C100,MATCH(Sheet2!B2,IF(Sheet1!A2:A100=Sheet2!A2,Sheet1!B2:B100,0),0))

array-entered.