formula: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" <[email protected]> wrote in message
news:%[email protected]...
> 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]

This makes no difference

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


"Patrick Simonds" <[email protected]> wrote in message
news:[email protected]...
> This makes no difference
>
> "Charles Harmon" <[email protected]> wrote in message
> news:%[email protected]...
>> Patrick,
>>
>> Try
>>
>> =If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
>> Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
>> "Patrick Simonds" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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


"Rob van Gelder" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> This makes no difference
>>
>> "Charles Harmon" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Patrick,
>>>
>>> Try
>>>
>>> =If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
>>> Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
>>> "Patrick Simonds" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> 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


"Patrick Simonds" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> This makes no difference
>>>
>>> "Charles Harmon" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> Patrick,
>>>>
>>>> Try
>>>>
>>>> =If(J7="",IF(AC8=0,VLOOKUP(J7,'[Work
>>>> Shifts.xls]Sunday'!$D$2:$K$100,8,0),AC8),"")
>>>> "Patrick Simonds" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> 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),"")
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>