nested if

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This vlookup works for the sheet its on, but if it doesn't find it I want it
to check the weekending for the 2 previous sheets respectively.

=VLOOKUP(B46,'07-17-07'!B45:I112,8,FALSE)
What I want to know is will this work to nest the if's.
if(=VLOOKUP(B46,'07-17-07'!B45:I112,8,FALSE),=VLOOKUP(B46,'07-10-07'!B45:I112,8,FALSE),=VLOOKUP(B46,'07-3-07'!B45:I112,8,FALSE))

tia,
 
You really need a construct like this:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlookup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

So, your formula would be:

=IF(ISNA(VLOOKUP(B46,'07-17-07'!
B45:I112,8,0)),IF(ISNA(VLOOKUP(B46,'07-10-07'!B45:I1­
12,8,0)),IF(ISNA(VLOOKUP(B46,'07-3-07'!B45:I112,8,0)),"not
present",VLOOKUP(B46,'07-3-07'!B45:I112,8,0)),VLOOKUP(B46,'07-10-07'!
B45:I1­12,8,0)),VLOOKUP(B46,'07-17-07'!B45:I112,8,0))

I've replaced FALSE with 0 to make it a bit shorter, but this is all
one formula so be wary of spurious linebreaks if copy/pasting from the
newsgroups.

Hope this helps.

Pete
 
Janis said:
This vlookup works for the sheet its on, but if it doesn't find it I
want it to check the weekending for the 2 previous sheets respectively. ....
What I want to know is will this work to nest the if's.

if(=VLOOKUP(B46,'07-17-07'!B45:I112,8,FALSE),
=VLOOKUP(B46,'07-10-07'!B45:I112,8,FALSE),
=VLOOKUP(B46,'07-3-07'!B45:I112,8,FALSE))
Click to expand...

You'd need something like

=IF(1-ISNA(VLOOKUP(B46,'07-17-07'!B45:I112,8,0)),
VLOOKUP(B46,'07-17-07'!B45:I112,8,0),
IF(1-ISNA(VLOOKUP(B46,'07-10-07'!B45:I112,8,0)),
VLOOKUP(B46,'07-10-07'!B45:I112,8,0),
IF(1-ISNA(VLOOKUP(B46,'07-3-07'!B45:I112,8,0)),
VLOOKUP(B46,'07-3-07'!B45:I112,8,0),"")))
 
One way:

=IF(ISNA(MATCH(B46,'07-17-07'!B45:B112, FALSE)),
IF(ISNA(MATCH(B46,'07-10-07'!B45:B112, FALSE)),
VLOOKUP(B46,'07-03-07'!B45:I112, 8, FALSE),
VLOOKUP(B46,'07-10-07'!B45:I112, 8, FALSE)),
VLOOKUP(B46,'07-17-07'!B45:I112,8,FALSE))
 
Back
Top