Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup with "many to many" relationship?
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? |
#2
|
|||
|
|||
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)) In article , "goofy11" wrote: 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? |
#3
|
|||
|
|||
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. In article , JE McGimpsey wrote: 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)) |
#4
|
|||
|
|||
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). "goofy11" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions | |||
vlookup. | Excel Worksheet Functions |