VLOOKUP returns #N/A

  • Thread starter Thread starter Alby
  • Start date Start date
A

Alby

Firstly, thank you all for your previous help.

I have a lookup formula:

=VLOOKUP(B20,'Driver Costs'!$A$2:$B$6,2,FALSE)*C20

but because there isn't any data in B20, it returns #N/A,

how do I blank this out until there is data in
B20....I've tried conditional format, but to no avail.

Regards

Alby
 
Alby,

A little background:
=IF(ISNA(yourlookup),0,yourlookup)
Is the basic construct of testing for the #N/A and if your Vlookup would
result
in #N/A then return a zero (can be changed to a blank by substituting two
double quotes for the 0)

For your formula (all one line):
=IF(ISNA(VLOOKUP(B20,'Driver
Costs'!$A$2:$B$6,2,FALSE)),"",VLOOKUP(B20,'Driver
Costs'!$A$2:$B$6,2,FALSE)*20)

John
 
Because I don't have your sheets, I can only guess at the code, but yo
could try modifications of this.

=IF((ISERROR(VLOOKUP(B20,'[Drive
Costs]Sheet1'!$A$2:$B$6,2,FALSE))),"",(VLOOKUP(B20,'[Drive
Costs]Sheet1'!$A$2:$B$6,2,FALSE))*C20)

Using If-Iserror tests for an error (such as #NA) and returns what yo
want if there is, or isnt an error. So, if there is an error, retur
"", if there isnt, return your formula.

I use it all the time for division formulas, to avoid bunches of #DIV!
errors.

Hope it helps.

-Bo
 
Back
Top