Need to look up both Horiz & Vert
Hello all,
I need to look up a value based on a date (vlookup) and a company and product (2 hlookups). I think I've done this once before, but it has been a while and I don't remember how I did it. Source table (sheet name is "Rates Paid"): --Column B has dates starting in row 3 --Row 1 has company starting in Col C, only 2 options: ABC & DEF --Row 2 has products starting in Col C, I'll use 4 for this example: Prod1, Prod2, Prod3, & Prod4 --companies repeat in row 1 & products repeat in row 2, but there is only unique values of product/company combinations (8 columns--4 products for each company). Destination table is a very similar setup, but unique combinations of product/company in different order and other columns inserted among them. My first question is, can SUMPRODUCT() be used to accomplish this? Example: Source Table's named ranges: "RP.Date" ='Rates Paid'!$B$3:$B$49 "RP.Comp" ='Rates Paid'!$C$1:$J$1 "RP.Product" ='Rates Paid'!$C$2:$J$2 "RP.Rates" ='Rates Paid'!$C$3:$J$20 Formula in destination table D3: =SUMPRODUCT((RP.Date=$B3)*(RP.Bank=D$1)*(RP.Produc t=D$2)*(RP.Rates)) Second question is, if SUMPRODUCT() no workie, then what are my other options? Thanks for any help anyone can provide, Conan Kelly |
Need to look up both Horiz & Vert
Kellt
What is RP.Bank? Try this =SUMPRODUCT((RP.Date=$B3)*--(RP.Bank=D$1)*--(RP.Product=D$2)*(RP.Rates)) With regards Sridhar "Conan Kelly" wrote: Hello all, I need to look up a value based on a date (vlookup) and a company and product (2 hlookups). I think I've done this once before, but it has been a while and I don't remember how I did it. Source table (sheet name is "Rates Paid"): --Column B has dates starting in row 3 --Row 1 has company starting in Col C, only 2 options: ABC & DEF --Row 2 has products starting in Col C, I'll use 4 for this example: Prod1, Prod2, Prod3, & Prod4 --companies repeat in row 1 & products repeat in row 2, but there is only unique values of product/company combinations (8 columns--4 products for each company). Destination table is a very similar setup, but unique combinations of product/company in different order and other columns inserted among them. My first question is, can SUMPRODUCT() be used to accomplish this? Example: Source Table's named ranges: "RP.Date" ='Rates Paid'!$B$3:$B$49 "RP.Comp" ='Rates Paid'!$C$1:$J$1 "RP.Product" ='Rates Paid'!$C$2:$J$2 "RP.Rates" ='Rates Paid'!$C$3:$J$20 Formula in destination table D3: =SUMPRODUCT((RP.Date=$B3)*(RP.Bank=D$1)*(RP.Produc t=D$2)*(RP.Rates)) Second question is, if SUMPRODUCT() no workie, then what are my other options? Thanks for any help anyone can provide, Conan Kelly |
All times are GMT +1. The time now is 10:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com