VLOOKUP function problem

[uplink600]

Please help

I am using the following vlookup function and I can't get it to work a
required.

=IF(D10<>"",(VLOOKUP(D10,Products!$A$7:$B$17,2,FALSE)),"")

I have a list of items on the 'Products' sheet and I am using the abov
function on the 'Orders' sheet. The Products list is as follows. Thi
is in the range A7:B17. Sorry if this doesn't display clearly on th
post

Code - Item
83201020- 80 x Sch40 API/A106 HFS Pipe SRL
83201220- 100 x Sch40 API/A106 HFS Pipe SRL
83201420- 150 x Sch40 API/A106 HFS Pipe SRL
83201620- 200 x Sch40 API/A106 HFS Pipe SRL
83201820- 250 x Sch40 API/A106 HFS Pipe SRL
83201930- 300 x Sch40 API/A106 HFS Pipe SRL
83201250- 100 x Sch80 API/A106 HFS Pipe SRL
83201450- 150 x Sch80 API/A106 HFS Pipe SRL
83201650- 200 x Sch80 API/A106 HFS Pipe SRL
83201850- 250 x SchXS API/A106 HFS Pipe SRL
83201950- 300 x SchXS API/A106 HFS Pipe SRL

When I try and use the function I only get the correct informatio
displayed for the first product (83201020). When I enter any other cod
I get the error #N/A.

Can anyone see whats wrong here. I would appreciate some help.

Thanks

 

[BrianB]

I guess it might be a formatting problem. You have to decide whether yo
want your codes to be numeric or text. I would favour numeric here.

Format your lookup cell and code column as required.
Select the column and from menu Data/Text to Columns/finish.


Formula for numeric :-
=IF(D10<>0,VLOOKUP(D10,Products!$A$7:$B$17,2,FALSE),0

 

[uplink600]

Thanks

Guess What

I format all the relevent columns as numerical and now I've got th
reverse hehe!!!

All the other codes now work fine and the first returns the erro
#N/A.

Any thoughts?

Thanks again for your reply.

V

 

[Frank Kabel]

Hi
now maybe your original value is formated as 'Text' alls numbers have
to be formated as Number (and changed accordingly)

 

[Rachel]

Did you try sorting your data. Lowest to Highest, a-z, etc?
I've had issues with Vlookup when my data is not sorted.

 

[Frank Kabel]

Hi
to prevent this problem set the 4th parameter of VLOOKUP to 'False'
This ensures an exact match