VLOOKUP for alpha/numeric items

[kgilliland]

I am trying to combine 2 reports, a vendor's new price list and a list of what we have in inventory. I want to create 1 report showing the old and new cost for our inventory. The problem is the item numbers contain both numbers and letters. And, somehow it seems that I can't get the data from the 2 reports to be formatted correctly so Excel recognizes something as simple as an exact numeric only match.

HELP - I've got to have this done today.

 

[NBVC]

Please show a sampling of your source data and your expected outputs.

 

[kgilliland]

Our list:

P/L Item Number Std Cost
WMH JWV-9 92
WMH K-125 143.65
WMH KB-1 0
WMH KDC100-JT2 56.25
WMH KDC100-JT33 56.25
WMH KDC130-JT33 64.5
WMH KDC130-JT6 64.5
WMH KDC130-MT3 86.25
WMH KDC130-MT4 90
WMH KDC160-JT3 73.5
WMH KDC160-JT6 73.5
WMH KDC160-MT3 111.75
WMH KDC160-MT4 116.25
WMH KDC80-MT2 63.75
WMH L2280-3 24,350.00

Vendor list (with column added using MATCH):
STOCK NO. Stock No. (use this column to sort alphanumeric) LIST NET/NET PRICE NON-STOCK?
JW2057 JW2057 $38.00 $19.00 FALSE
JW2058 JW2058 $62.00 $31.00 FALSE
JW2059 JW2059 $158.00 $79.00 FALSE
JW2060 JW2060 $170.00 $85.00 FALSE
JW2061 JW2061 $54.00 $27.00 FALSE
JW2062 JW2062 $60.00 $30.00 FALSE
JW2063 JW2063 $74.00 $37.00 FALSE
KDC100-JT33 KDC100-JT33 $75.00 $56.00 FALSE
KDC130-JT33 KDC130-JT33 $87.00 $65.00 FALSE
KDC130-JT6 KDC130-JT6 $87.00 $65.00 FALSE
KDC130-MT3 KDC130-MT3 $116.00 $87.00 FALSE

 

[kgilliland]

HELP?

 

[Todd Bardoni]

Check for trailing/leading spaces first.

 

[NBVC]

I don't get what you are doing?

Please explain, in words, what you are wanting to do and refer to one of the items that appears in both lists that you posted.

Also post the formula that you are using currently.

Thanks.

 

[kgilliland]

I want to create 1 report showing the old and new cost for our inventory.
We do not list the vendor's entire line so I need a way to take each item#, look for it in their list and bring back the new cost.

=VLOOKUP(B2,VENDOR!B2:B4517,3)

P/L Item Number Std Cost
WMH 20 1.12 #REF!

STOCK# Stock#(txt)LIST NET/NET PRICE
20 20 $2.50 $1.15
21 21 $2.98 $1.35

 

[NBVC]

Not sure if it's that simple, but formula should be:

=VLOOKUP(B2,VENDOR!$B$2:$D$4517,3,0)

You need to include the column your extracting from in the lookup table range...and you probably need the 0 argument to get the exact match.

You should also "freeze" the table range by adding the $-signs so that you can copy the formula down the column without affecting the table range.

 

[kgilliland]

IT WORKED!!!!!!!!!!!!!!!!!!!!!!
THANK YOU, THANK YOU, THANK YOU!!!!!!!!!!!!