IF statement with multiple VLOOKUPs
 

Despite many searches I've been unable to embed multiple nested 'IF' formulas in VLOOPKUP. And I'm starting to wonder if my problem is solvable.

The following 3 sheets (2, 3 and 4) contain data regarding the 600+ names, here illustrated as 6 names (Tom, William, Harry, John, Mary and Joyce)
sheet 2 Tom data (sheet 2) William data (sheet 2) John data (sheet 2) Joyce data (sheet 2) and Mary NO DATA
sheet 3 William data (sheet 3) Mary data (sheet 3)
sheet 4 Harry data (sheet 4) Joyce data (sheet 4)

Sheet 2 has the most recent info, then sheet 3, sheet 4 contains the oldest data

What I want to obtain in sheet 1 is: if the name and associated data in sheet 2 is <"", get it from sheet 2, if the name is not found OR when the name is found but the associated data is not, go to sheet 3, etc.

Sheet 1 Want to achieve What I got
Tom Tom data (sheet 2) Tom data (sheet 2)
William William data (sheet 2) William data (sheet 2)
Harry Harry data (sheet 4) #N/A
John John data (sheet 2) John data (sheet 2)
Mary Mary data (sheet 3) Mary data (sheet 3)
Joyce Joyce data (sheet 2) Joyce data (sheet 2)

I can't apply a simple 'IF' formula, since there are 600+ names and they are not on the same rows.

I've tried VLOOKUP, but got #N/A response (see Harry):
=IF(VLOOKUP(A13,Sheet2!$A$1:$B$8,2,0)<””,VLOOKUP( A13,Sheet2!$A$1:$B$8,2,0),IF(VLOOKUP(A13,Sheet3!$A $1:$B$8,2,0)<””,VLOOKUP(A13,Sheet3!$A$1:$B$8,2,0) ,IF(VLOOKUP(A13,Sheet4!$A$1:$B$8,2,0)<””,VLOOKUP( A13,Sheet4!$A$1:$B$8,2,0),"No Matching Data Found")))

I've tried IFERROR but got the error message "too many arguments":
=IFERROR(VLOOKUP(A13,Sheet2!$A$1:$B$8,2,0),VLOOKUP (A13,Sheet2!$A$1:$B$8,2,0),IFERROR(VLOOKUP(A13,She et3!$A$1:$B$8,2,0),VLOOKUP(A13,Sheet3!$A$1:$B$8,2, 0),IFERROR(VLOOKUP(A13,Sheet4!$A$1:$B$8,2,0),VLOOK UP(A13,Sheet4!$A$1:$B$8,2,0)))

I've tried =IFERROR(VLOOKUP(A13,Sheet2!$A$1:$B$8,2,0),IFERROR (VLOOKUP(A13,Sheet3!$A$1:$B$8,2,0),IFERROR(VLOOKUP (A13,Sheet4!$A$1:$B$8,2,0),"")))
but when the name is there e.g. Mary' name be on sheet 2, but the associated data for Mary is not, it returns an unwanted '0' instead of checking the next sheet.

I hope someone can help me, thank you in advance,

Gijs

Hi,

Would there only ever be the three sheets to lookup against, or could there possibly be more?

If more, how many max?

Thank you Spencer for helping me. The maximum is 5 sheets.

Gijs

Your current formula appears to work if every name is on each sheet.
The fact that Harry does not appear on Sheet2 or Sheet3 means the formula stumbles with an #N/A at that point and cannot carry on.

Leave it with me and I shall see what I can do for you.

S.

That would be great Spencer. Looking forward to it,

Gijs