Problem with IF statement

[Patrick Simonds]

In the formula below if J7 is blank the result should be a blank cell, but
it is returning #N/A because the VLOOKUP is finding a blank cell when it
does it's lookup. Why is it performing the VLOOKUP and displaying #N/A when
the IF statement is False?

=IF(J7>0,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

 

[Charles Harmon]

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

 

[Patrick Simonds]

This makes no difference

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

In the formula below if J7 is blank the result should be a blank cell,
but it is returning #N/A because the VLOOKUP is finding a blank cell when
it does it's lookup. Why is it performing the VLOOKUP and displaying #N/A
when the IF statement is False?

=IF(J7>0,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

 

[Rob van Gelder]

VLOOKUP will return #N/A when it doesn't find a match

If you want a blank shown instead of #N/A then you need to do something like
=IF(ISNA(VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0)), "",
VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0))

I wish there was a REPLACENA function, but there isn't

--
Rob van Gelder - http://www.vangelder.co.nz/excel

This makes no difference

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

In the formula below if J7 is blank the result should be a blank cell,
but it is returning #N/A because the VLOOKUP is finding a blank cell
when it does it's lookup. Why is it performing the VLOOKUP and
displaying #N/A when the IF statement is False?

=IF(J7>0,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

 

[Patrick Simonds]

But in this case the IF Statement returns a false and the VLOOKUP result
should not be displayed. The false result should be a blank ("") cell

VLOOKUP will return #N/A when it doesn't find a match

If you want a blank shown instead of #N/A then you need to do something
like
=IF(ISNA(VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0)), "",
VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0))

I wish there was a REPLACENA function, but there isn't

--
Rob van Gelder - http://www.vangelder.co.nz/excel

This makes no difference

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
In the formula below if J7 is blank the result should be a blank cell,
but it is returning #N/A because the VLOOKUP is finding a blank cell
when it does it's lookup. Why is it performing the VLOOKUP and
displaying #N/A when the IF statement is False?

=IF(J7>0,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

 

[Rob van Gelder]

Check that J7 is not blank
Check that AC8 is not 0 (or blank)

--
Rob van Gelder - http://www.vangelder.co.nz/excel

But in this case the IF Statement returns a false and the VLOOKUP result
should not be displayed. The false result should be a blank ("") cell

VLOOKUP will return #N/A when it doesn't find a match

If you want a blank shown instead of #N/A then you need to do something
like
=IF(ISNA(VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0)), "",
VLOOKUP(J7,'[Work Shifts.xls]Sunday'!$D$2:$K$100,8,0))

I wish there was a REPLACENA function, but there isn't

--
Rob van Gelder - http://www.vangelder.co.nz/excel

This makes no difference

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
In the formula below if J7 is blank the result should be a blank cell,
but it is returning #N/A because the VLOOKUP is finding a blank cell
when it does it's lookup. Why is it performing the VLOOKUP and
displaying #N/A when the IF statement is False?

=IF(J7>0,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")