Excel - vlookup for letter characters?

andrewjgrimaldi

New Member
Joined
Jan 19, 2006
Messages
28
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

andrewjgrimaldi

New Member
Joined
Jan 19, 2006
Messages
28
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Hope this gets you started in the right direction,
Book1
ABCDEF
1Pro 2006AmtPro 2005Amt3
2111111132
311211122 
411611151 
Sheet1
 

andrewjgrimaldi

New Member
Joined
Jan 19, 2006
Messages
28
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

New Member
Joined
Aug 23, 2006
Messages
23
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,793
Messages
5,544,315
Members
410,602
Latest member
lidovi
Top