VLOOKUP with multiple criteria across multple tables

Good Afternoon,

Currently I have 2 formulas that I wish to nest together but cannot figure out how to do it.

At the moment, i have 2 tables (diff sheets) with first name and surname as the lookup criteria and then 1 value to return if they match.

However, I wish to do this across multiple tables and if the person cannot be found in the first table, to move to the next. Please could you assist?

Below is a formula that allows multiple sheets and tables.

Please Login or Register  to view this content.

Below is a formula that allows multiple criteria lookup.

Please Login or Register  to view this content.

If you need to merge the persons name to achieve this, please only merge in the lookup box and not the main dable as the first and surname must remain different.

Your help would be much appreciated.

VLookup across multi tables and criteria.xlsx



Kind Regards,
Quasis

You get better help if you add an excel file, without confidential information.

Please also add the expected result in your file.

Based on your sample, try this in H2...
=IFERROR(IFERROR(INDEX(Sheet1!$C$2:$C$6,MATCH(Sheet1!E2&" "&Sheet1!F2,INDEX(Sheet1!$A$2:$A$6&" "&Sheet1!B2:B6,0),0)),INDEX(Sheet2!$C$2:$C$6,MATCH(Sheet1!E2&" "&Sheet1!F2,INDEX(Sheet2!$A$2:$A$6&" "&Sheet2!B2:B6,0),0))),"Not in Table")