VLOOKUP numbers as #N/A - slowly losing the will....

[Potsy]

Hi

Having a few issues with VLOOKUP, basically if Part Numbers (ColumnD)
contains text and numbers it is okay, hoever, rows that just contain
numbers only or numbers and slashes or dots are showing as #N/A in
ColumnE, have searched on here (http://www.contextures.com/
xlFunctions02.html) and tried looking at following but does not seem
to work. any help appreciated!!!

Here is formula currently:

=VLOOKUP(D2,CommodityCode,4)

'CommodityCode' is named Range on another sheet in same workbook

Here are results for column D and E respectively:

Part Number Commodity
GW Small Power
GU7271BSS Small Power
1000/8 #N/A
E3PP158 Mains Distribution
GW Small Power
2035.802 #N/A
8002.01 #N/A
GU7271BSS Small Power
010-100-100-08 #N/A
7027 #N/A
LMA10 Lighting

Any help appecated!!!

Potsy

 

[Dave Peterson]

Maybe you have text numbers in one spot and number numbers in the other spot.

Look at Debra Dalgleish's site for troubleshooting your =vlookup() formula:
http://contextures.com/xlFunctions02.html#Trouble

And since you're matching on text at least some of the time, I would bet that
you want false as that 4th argument.

 

[Guest]

I get this to work for all of your examples by using this formula:
=VLOOKUP(A2,$I$1:$J$9,2,FALSE).

Two things to always make sure of with vlookups:

1. make sure your range is sorted by the index column (that is make sure
your "Commodity Code" range is sorted by its first column.

2. Use the last option argument "false" to make sure you are getting exact
matches.

My test
In cells A1 to C4:

Part Number
GW b
1000/8 a
8002.01 c

in cells I1 to J:3

8002.01 c
1000/8 a
GW b



Note that when excel sorts them, 8002.01 (a number) comes before 1000/8 (a
string).

 

[Potsy]

I get this to work for all of your examples by using this formula:
=VLOOKUP(A2,$I$1:$J$9,2,FALSE).

Two things to always make sure of with vlookups:

1. make sure your range is sorted by the index column (that is make sure
your "Commodity Code" range is sorted by its first column.

2. Use the last option argument "false" to make sure you are getting exact
matches.

My test
In cells A1 to C4:

Part Number
GW b
1000/8 a
8002.01 c

in cells I1 to J:3

8002.01 c
1000/8 a
GW b

Note that when excel sorts them, 8002.01 (a number) comes before 1000/8 (a
string).











- Show quoted text -

sorry cannot seem to get to work can I send file?