# VLOOKUP for alpha/numeric items

#### kgilliland

##### New Member
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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

HELP?

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.

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

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.

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

Replies
4
Views
317
Replies
1
Views
125
Replies
31
Views
726
Replies
3
Views
222
Replies
0
Views
120

1,203,667
Messages
6,056,647
Members
444,879
Latest member
suzndush

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back