Lookup based on Two Variables in Multiple Worksheets

Hi all,

Please could someone assist me, as I'm struggling with the following? thanks!

- I have 3 tabs: Combined, Type 1 and Type 2
- Each tab includes data based on 3 columns: Region, Identification number and Service Start/Change Date

I would like the 'Combined' tab to display the correct 'Service Start/Change Date' based on the 'Region' and 'Identification Number' from both 'Type 1' and 'Type 2' tabs, respectively.

I've tried using VLOOKUP but having looked on-line I think INDEX & MATCH or INDIRECT is a better option, unless I'm mistaken?

I've attached the spreadsheet. Any feedback is greatly appreciated!

Regards
jusmightmightbeokay

Put this array* formula in cell C2 of the Combined sheet:

=IFERROR(INDEX('Type 1'!C$2:C$90,MATCH(A2&B2,'Type 1'!A$2:A$90&'Type 1'!B$2:B$90,0)),IFERROR(INDEX('Type 2'!C$2:C$90,MATCH(A2&B2,'Type 2'!A$2:A$90&'Type 2'!B$2:B$90,0)),""))

then copy down.

NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

That is spectacular, many thanks Pete_UK.

To XOR LX, the Region and Identification number should be consistent. After adding the array formula I can see I have an error with one of the Identification numbers, namely Y13-043, which should read as Y12-043!!

Many thanks again for the swift response.

Regards
jusmightbeokay