spread sheets vlookup

[Guest]

I need some help with vlook up.. I cannot seem to get it right. i have to
make it to where when i put a number in it also puts in the description and
unit price just by the number i put in to a cell.. can anyone please help
me.. I am making an invoice... and using a look up table to do the vlookup
function but dont know what i am doing wrong someone please help me asap...

thanks

 

[Ragdyer]

Check out this web page of Debra Dalgleish that should help you:

http://www.contextures.com/xlFunctions02.html

 

[Guest]

I am actualy on that sitte now.. and i still dont see what i am doing wrong.

 

[Biff]

Try explaining what you want to do and include what formulas you have tried,
where the data is and what type of data it is.

The more details you provide the better chance you have of getting help that
solves the problem.

Details.........Details..........Details!!!

Biff

 

[Gord Dibben]

Show us your VLOOKUP formulas and the layout of your lookup table.

To get price and description you need two VLOOKUP formulas.

The table should be at least 3 columns.........number, price, description.


Gord Dibben MS Excel MVP

 

[Guest]

I am doing an invoice but put it this way,

cell A6 is validated with only certain #'s
cell b6 is description
cell c6 is quantity
cell d6 isunit price
cell e6 is extended price

now using vlookup for b6 and d6. okay now it returns a #N/A, but we are
suppose to have it a blank cell so i used
=IF(ISNA(VLOOKUP(A14,products,2)),"",VLOOKUP(A14,products,2)), okay this made
the #n/a go blank in the cell perfect... but in cell e6 now it says
#value...I think it is suppose to be blank as well. how can i make it blank
it also has a formula in it which is =c6*d6, but when i chose cell a6 and put
in the number i am suppose to everything is correct there. what do i do about
the #value or is it suppose to be there until the info is put into cell
a6???????????????????
Please help me as soon as possible...

thanks
joyce oh i am using excel 2003

 

[Dave Peterson]

Since you're matching on part numbers, I would think you'd want a formula like:

=if(isna(vlookup(a4,products,2,false)),"",vlookup(a4,products,2,false))

That 4th argument (False) forces an exact match.

Then to get the extended price, you could use:

=if(d4="","",d4*c4)
or
=if(d4="",0,d4*c4)
(if you want to show the extended price as 0.

 

[Guest]

Hi Dave
thanks that worked, I dont know why i didnt think of that one... maybe i
was thinking that those cells werent suppose to have an if statement.. But it
looks good and still get the correct answers.. I soooooooooo appreciate it...
the first if statement you gave, does it really make a difference if you put
false in it??? cause without it , it comes up with the same thing. Thanks
again..
Joyce

 

[Dave Peterson]

If I want an exact match, I'd add False.

I think if you test some bad part numbers or sort your table differently, you'll
see the problem.

Do your tests against a copy of the original workbook.