Help with a complex formula

[Dave Potter]

Hi,

I'm having trouble figuring out how to set a second condition within a
formula where one condition is met but I need another condition in the
formula such that even if the first condition is true, if the second
condition is not true I want the result to be a blank cell. Here is
whats going on.

In cell B8 is this formula

=IF(ISBLANK($A8)," ",VLOOKUP($A8,MON!$A$11:$C$29,2,FALSE))


I enter a name in A8 and if it finds that name in MON!$A$11:$C$29 it
returns the value in column 2 of the range. (This part works fine)

When i go to K8 and enter this formula

=IF(ISBLANK($A8)," ",VLOOKUP($A8,TUE!$A$11:$C$29,2,FALSE))

the result in N/A because in the range TUE!$A$11:$C$29 the name is not
found.

How can i write the formula in K8 so that even though $A8 is NOT
BLANK, if the name in $A8 is not found in TUE!$A$11:$C$29 it will
return a blank cell?


I will be monitoring responses and will answer any questions that will
help clarify the situation. Any and all help would be greatly
appreciated.

Thanks so much,
David

 

[Aladin Akyurek]

See:

http://makeashorterlink.com/?Z19E515F5

 

[Jean-Paul Viel]

Hi,

Sorry, I forget something in the formula:



=IF(and(ISBLANK($A8), VLOOKUP($A8,TUE!$A$11:$C$29,1,FALSE)=$A8,"
",VLOOKUP($A8,TUE!$A$11:$C$29,2,FALSE))

 

[Dave Smith]

In this case you can get by without a second condition by doing this:

=IF(ISNA(VLOOKUP($A8,MON!$A$11:$C$29,2,FALSE)),"",VLOOKUP($A8,MON!$A$11:$C$2
9,2,FALSE))

However, if you ever do need a second condtional you can do something like
this:

=IF(AND(A1=1, B1=2),C1,C2)

or

=IF(OR(A1=1, B1=2),C1,C2)

or even

=IF(A1=1,IF(B1=2,C1,C3),C3)

HTH

-Dave

 

[Dave Potter]

Thanks Alan,
That worked like a charm!
David

Instead of VLOOKUP(whatever) use
IF(ISNA(VLOOKUP(whatever)),"",VLOOKUP(whatever))

By the way, you might want to reconsider using " " as a return value
instead of "". A cell with " " in it is not a blank cell, although it
appears blank on the worksheet.

Alan Beban

 

[Alan Beban]

Hard to see what you're attempting. If the first VLOOKUP returns #N/A
that's because the value from A8 was not found in the lefthand column of
the range; in that case the second VLOOKUP will also return #N/A.

=IF(ISBLANK(VLOOKUP($A8,MON!$A$11:$C$29,2,FALSE),VLOOKUP($A8,MON!$A$11:$C$29,3,FALSE),VLOOKUP($A8,MON!$A$11:$C$29,,FALSE)))

will return #N/A if the value from Cell A8 is not found; otherwise it
will return the value from the 3rd column if the second-column value is
blank, otherwise the value from the second column.

Alan Beban

 

[Tom Ogilvy]

=IF(ISNA(VLOOKUP($A8,Mon!$A$11:$C$29,1,FALSE)),"",IF(VLOOKUP($A8,Mon!$A$11:$
C$29,2,FALSE)="",VLOOKUP($A8,Mon!$A$11:$C$29,3,FALSE),VLOOKUP($A8,Mon!$A$11:
$C$29,2,FALSE)))

would be one way.

Regards,
Tom Ogilvy