Excel - vlookup for letter characters?

[andrewjgrimaldi]

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

Thanks a lot in advance for any help you can provide me with.

 

[Brian from Maui]

What would you like to happen when there is a match/no match?

 

[andrewjgrimaldi]

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.

 

[Brian from Maui]

Hope this gets you started in the right direction,

Book1
	A	B	C	D	E	F
1	Pro 2006	Amt	Pro 2005	Amt		3
2	111	1	111	3		2
3	112	1	112	2
4	116	1	115	1
Sheet1

 

[andrewjgrimaldi]

Hi,

Thanks for your reply.

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?

I really appreciate your help/time.

Thanks again.

 

[Tyarra]

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?