VLOOKUP PROBLEM

  • Thread starter Thread starter 1NetAdminGuy
  • Start date Start date
1

1NetAdminGuy

I have a VLOOKUP formula in Excel 2000 that is doing what I want it to
do, but I don't know that it's going to work correctly for the whole
project. The short of what i am trying to do is i have a sheet that
has 2 columns. the first is a vendor ID and the second is a percentage
that we take off of retail price from that vendor. Basically what i
would like to do is on another worksheet, be able to enter a vendor ID
that will then lookup their discount percentage and display it in the
cell next to it. i then do various calculations from there. this works
perfectly if i only enter one vendor id and get all the prices. but if
i want to fill that down like 25 rows so i could enter a possible 25
IDs and get a bunch of prices and totals, then of course i get the
#N/A in all of them until i fill in a value. and it also gives the
#N/A in the SUM cells because all of them aren't filled in. I was
wondering if there was a way to not have these #N/A show up and still
calculate totals if i don't fill in all 25 rows?

Here is the VLOOKUP formula i am using right now:
=VLOOKUP(A4,Vendors!A1:B4,2,FALSE)

Like I said, it works fine if i just use 1 vendor, but i want to fill
that down to have a possible 25 entries and total the columns with
however many i have filled in....and not have the #N/A show up and
disrupt this process.

Thanks for any help.

Brian Mosher
 
Hi
try the following:
=IF(A4<>"",VLOOKUP(A4,Vendors!$A$1:$B$4,2,FALSE),"")
and copy down
 
Frank Kabel said:
Hi
try the following:
=IF(A4<>"",VLOOKUP(A4,Vendors!$A$1:$B$4,2,FALSE),"")
and copy down
Click to expand...
SUCCESS! thanks Frank. I had tried a few different variations of an IF
statement with a Null cell, but for some reason kept getting invalid
formula errors. I must have been leaving out some minute detail.
Thanks again for your solution.

Brian Mosher
 
Hi Brian,
If you were using testing with ISBLANK(A4) then you
would always get a False condition for a cell with a
formula as well as a cell with spaces.

If you have to protect yourself against someone "clears"
cells by entering spaces, you might have to resort to using
something like =IF(TRIM(A4)<>"", ...
if they won't use the Del key or the menu to actually clear cells.
 
Back
Top