Need to look up both Horiz & Vert

[Conan Kelly]

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

 

[yshridhar]

Kellt
What is RP.Bank?
Try this
=SUMPRODUCT((RP.Date=$B3)*--(RP.Bank=D$1)*--(RP.Product=D$2)*(RP.Rates))
With regards
Sridhar