formatting a column with vlookup

[Laurie]

I have a worksheet setup using Data Validation & VLookup. Example:

Qty Description Builder Total

When putting in the desc (which is a dropdown list using data
validation) after selecting the product the builder price
automatically goes in the next column (builder)using vlookup. On the
data validation , I have the drop down list with the first entry as a
blank line, but this puts #N/A in the builder column. I would like to
have $0.00 there if possible. Thanks

 

[chadt74]

I'm sure there is a better/cleaner way to do this, but I have the same
problem (or having a vlookup not finding its value and returning an
NA) and I use this formula..

=IF(ISERROR(VLOOKUP(B2,$G$2:$H$7,2,FALSE))=TRUE,0,VLOOKUP(B2,$G$2:$H$7,2,FALSE))

Basically you are using an if statement and IF the vlookup returns an
error (the Iserror function) then it drops a zero in the cell but if
it finds a value then it returns that value.

Of course my formula assumes that
1 - this formula would be in cell Column C on your example
2 - Description is in Column B on your example
3 - The lookup range is in columns G&H (Builder and price)

I hope this helps and let me know if I can be of any more assistance.

Chad

 

[Tony]

I'm sure there is a better/cleaner way to do this, but I have the same
problem (or having a vlookup not finding its value and returning an
NA) and I use this formula..

=IF(ISERROR(VLOOKUP(B2,$G$2:$H$7,2,FALSE))=TRUE,0,VLOOKUP(B2,$G$2:$H$7,2,FALSE))

Basically you are using an if statement and IF the vlookup returns an
error (the Iserror function) then it drops a zero in the cell but if
it finds a value then it returns that value.

Of course my formula assumes that
1 - this formula would be in cell Column C on your example
2 - Description is in Column B on your example
3 - The lookup range is in columns G&H (Builder and price)

I hope this helps and let me know if I can be of any more assistance.

Chad

Hi, its Dave.
I like that formula, its pretty usefull.
Is there a way to tell it to change the color of the cell if it didn't
find the entry,
ie it didn't think the entry so the formula is going to return 0 but
also change the color to red.

Thanks, Dave

 

[chadt74]

Dave with the assumption being that the only time it would return a
zero was when you did not find the supplier (why else would the part
be zero?) then you can use conditional formating [FORMAT>>CONDITIONAL
FORMATING] and set all the cells in the buider column to CELL VALUE IS
EQUAL TO 0 (zero) and then click on the FORMAT button and you can
chose how you want the cell to format if it is zero.

now IF you have some VLOOKUP values that will return a zero, in your
IF statement you might want to return something like .000001 instead
of zero and then have the Conditional formating look for that amount
since that should not effect you're amounts in anyway.

Hope this is clear and good luck

Chad