# Excel - vlookup for letter characters?

#### andrewjgrimaldi

##### New Member
Hello.

I have sales information by product name for two years. However, in there are differences from year1 and year2 (i.e. new products, or old products that no longer exist for 2006). I would like to compare products of the same name from FY 06 to FY 05. Therefore, I tried doing a VLookup function. However, I don't think VLookup can work when the thing you are looking up is letter characters (i.e. look at product name, then pull dollar amount if name matches won't work because name is letters and not numbers)?

I wanted to take information from FY 2005 and match similar products to FY 2006 numbers. Can someone help me determine how to do this?

The information I have is something like this:

Fiscal Year 2006 FY 2005
Product_111 \$20.83 Product_111 \$20.50
Product_112 \$30.00 Product_112 \$29.95
Product_116 \$33.33 Product_115 \$12.00 (sequence difference)
Product_119 \$3.20 Product_116 \$29.99
Product_121 \$15.20 Product_117 \$33.89 (sequence difference)
and so on... and so on.....

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
What would you like to happen when there is a match/no match?

Hi,

Sorry for not making it so clear.

when there is a match from the FY 06 product name to the FY 05 product name, I would like to pull over the FY 05 ending balance.

Thanks.

Hope this gets you started in the right direction,
Book1
ABCDEF
1Pro 2006AmtPro 2005Amt3
2111111132
311211122
411611151
Sheet1

Hi,

Unfortunately though, the solution it doesn't seem to work. I think it is because I am dealing with looking up characters. For instance, you have the product id (101, 102, 103), but the way I have the information is by name (ProductNameA, ProductNameB, ProductName C). Do you have any further suggestions?

Thanks again.

VLOOKUP should work fine with letters.

Assumptions here:
Column A is FY06 product names.
Column B is FY06 balances.
Column C is FY05 product names.
Column D is FY05 balances.

Select Columns C&D, to include all of FY05 products and balances. From the Insert menu chose Names and Define. In the box (highlighted, and stuff will already be there) type in a name for your range. FYear05 is simple enough.

In Column E, across from your first FY06 product (assuming A1, change if different):
=VLOOKUP(A1, FYear05,2,FALSE)
Copy down to include all rows.

If there is no product in FY 2005 for the item in FY2006, you'll get #NA - presumably a new product for 2006.

You can reverse this, to look for items that aren't in FY2006 that were there in FY2005, discontinued, perhaps. (that is, define a range for FYear06, and change the VLOOKUP to reference the FY05 product name against that range)

Is this closer to what you want?

Replies
2
Views
1K
Replies
4
Views
1K

1,214,720
Messages
6,121,133
Members
449,012
Latest member
Lilcps

### 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