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

  • Thread starter Thread starter Potsy
  • Start date Start date
P

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
 
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).
 
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 -
Click to expand...

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