Vlookup alternative

[Irwell1878]

I have a three tab spreadsheet.

Tab 1 has

ID - First name - Surname - Review Date

Tab 2 has

ID - First Name - Surname - Exit Date

I want tab 3 to bring them both together so to speak

ID - First Name - Surname - Review Date - Exit Date

However the problem is that many of clients have the same ID number so Joe Bloggs and Jane Doe may both have ID of 25 so a Vlookup on ID wouldn't work. This is going to be a regular report with tabs 1 and 2 just having stuff dropped into them to feed into the summary tab so ideally I want no editing to these tabs at all.

Suggestions?

 

[Nine Zero]

do an INDEX MATCH for the 2 columns so for example assuming all ID = column A, First Name = column B, and Surname = column C and the headers begin at row 1

in cell D2 in Tab 3
=INDEX(Tab1!$D$2:$D$500,MATCH($A1&$B1&$C1,Tab1!$A$2:$A$500&Tab1!$B$2:$B$500&Tab1!$C$2:$C$500,0))

in cell E2 in Tab 3
=INDEX(Tab2!$D$2:$D$500,MATCH($A1&$B1&$C1,Tab2!$A$2:$A$500&Tab2!$B$2:$B$500&Tab2!$C$2:$C$500,0))

change the 500 to whatever you please, just dont use the entire column as this will slow down the sheet. Use a safe number like 5000 if you know yo will never reach 5000

***IMPORTANT***
These are both array formulas so instead of just hitting ENTER when entering them, use CTRL + SHIFT + ENTER

***EDIT: forgot to add the $ to the rows on the sheets.... they have been added now

 

[Irwell1878]

do an INDEX MATCH for the 2 columns so for example assuming all ID = column A, First Name = column B, and Surname = column C and the headers begin at row 1

in cell D2 in Tab 3
=INDEX(Tab1!$D$2:$D$500,MATCH($A1&$B1&$C1,Tab1!$A$2:$A$500&Tab1!$B$2:$B$500&Tab1!$C$2:$C$500,0))

in cell E2 in Tab 3
=INDEX(Tab2!$D$2:$D$500,MATCH($A1&$B1&$C1,Tab2!$A$2:$A$500&Tab2!$B$2:$B$500&Tab2!$C$2:$C$500,0))

change the 500 to whatever you please, just dont use the entire column as this will slow down the sheet. Use a safe number like 5000 if you know yo will never reach 5000

***IMPORTANT***
These are both array formulas so instead of just hitting ENTER when entering them, use CTRL + SHIFT + ENTER

***EDIT: forgot to add the $ to the rows on the sheets.... they have been added now

Thanks for this, the formula works, I just can't remember which project I needed it on. I'll come back to it next week no doubt. Thank you.