G
GijsKijlstra
Despite many searches I've been unable to embed multiple nested 'IF
formulas in VLOOPKUP. And I'm starting to wonder if my problem i
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 an
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 th
oldest data
What I want to obtain in sheet 1 is: if the name and associated data i
sheet 2 is <>"", get it from sheet 2, if the name is not found OR whe
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 the
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),"N
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,Sheet3!$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),VLOOKUP(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(VLOOKU
(A13,Sheet4!$A$1:$B$8,2,0),"")))
but when the name is there e.g. Mary' name be on sheet 2, but th
associated data for Mary is not, it returns an unwanted '0' instead o
checking the next sheet.
I hope someone can help me, thank you in advance,
Gij
+-------------------------------------------------------------------
|Filename: IF statement with multiple VLOOKUPs.xls.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=396
+-------------------------------------------------------------------
formulas in VLOOPKUP. And I'm starting to wonder if my problem i
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 an
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 th
oldest data
What I want to obtain in sheet 1 is: if the name and associated data i
sheet 2 is <>"", get it from sheet 2, if the name is not found OR whe
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 the
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),"N
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,Sheet3!$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),VLOOKUP(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(VLOOKU
(A13,Sheet4!$A$1:$B$8,2,0),"")))
but when the name is there e.g. Mary' name be on sheet 2, but th
associated data for Mary is not, it returns an unwanted '0' instead o
checking the next sheet.
I hope someone can help me, thank you in advance,
Gij
+-------------------------------------------------------------------
|Filename: IF statement with multiple VLOOKUPs.xls.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=396
+-------------------------------------------------------------------