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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
"Patrick Simonds" wrote in message
...
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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

This makes no difference

"Charles Harmon" wrote in message
...
Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
"Patrick Simonds" wrote in message
...
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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

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


"Patrick Simonds" wrote in message
...
This makes no difference

"Charles Harmon" wrote in message
...
Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
"Patrick Simonds" wrote in message
...
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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

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


"Rob van Gelder" wrote in message
...
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


"Patrick Simonds" wrote in message
...
This makes no difference

"Charles Harmon" wrote in message
...
Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
"Patrick Simonds" wrote in message
...
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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")

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

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


"Patrick Simonds" wrote in message
...
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


"Rob van Gelder" wrote in message
...
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


"Patrick Simonds" wrote in message
...
This makes no difference

"Charles Harmon" wrote in message
...
Patrick,

Try

=If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
"Patrick Simonds" wrote in message
...
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(J70,IF(AC8=0,VLOOKUP(J7,'[Work
Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")