Vlookup with "many to many" relationship?

[Guest]

Here's what I'd like to do, but am not sure if it can be done in Excel. I
have a worksheet with sales data by month and by item (duplicate months and
duplicate items). In database language, I believe this would be described as
a many to many relationship. I want to do a vlookup (or alternative) for the
month AND the item (2 columns). When it finds the row with the correct month
and item, then return the sales number. Here is a simple example.

Month Item # Sales
May 2222 100
May 4444 200
June 2222 110
June 4444 190

Let's say I want to return the Sales value for item 4444 in May. It needs
to look for May in column A, then look for item 4444 in column B. When it
finds the match, then return the corresponding value in column C. Normally I
would do something like this in Access, but in this case I have been given an
Excel template that I need to use. Can this be done?

 

[JE McGimpsey]

One way (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

Assume that your target month is in E1 and your target item is in E2:

=INDEX(C1:C5,MATCH(E1&E2,A1:A5&B1:B5,0))

 

[JE McGimpsey]

Hit send too early.

Since your data in Column C is numeric, you can also use

=SUMPRODUCT(--(A1:A5=E1),--(B1:B5=E2),C1:C5)

entered normally.

See

http://www.mcgimpsey.com/excel/doubleneg.html

for the usage of "--" in the formula.

 

[Guest]

How about a pivot table?
in the row section put in month and item #, in the data section put in sales
(becomes sum of sales).