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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Potsy - 13 Sep 2007 15:35 GMT

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

Reply to this Message

Dave Peterson - 13 Sep 2007 16:06 GMT

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.

> Hi
>
[quoted text clipped - 29 lines]
>
> Potsy

Signature

Dave Peterson

Reply to this Message

mcleester - 13 Sep 2007 16:12 GMT

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).

> Hi
>
[quoted text clipped - 29 lines]
>
> Potsy

Reply to this Message

Potsy - 14 Sep 2007 09:29 GMT

On 13 Sep, 16:12, mcleester <mclees...@discussions.microsoft.com>
wrote:

> I get this to work for all of your examples by using this formula:
> =VLOOKUP(A2,$I$1:$J$9,2,FALSE).
[quoted text clipped - 59 lines]
>
> - Show quoted text -

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

Reply to this Message

Rate this thread: