|
Problem with IF statement
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),"") |
Problem with IF statement
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),"") |
Problem with IF statement
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),"") |
Problem with IF statement
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),"") |
Problem with IF statement
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),"") |
Problem with IF statement
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),"") |
| All times are GMT +1. The time now is 01:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com