Lookup Question

[mitchman7614]

I have data that requires a rate to be assigned to it according the a bill-to code and a product identifier. I thought to use a v-lookup, but don't think that will work since I have to look at two values before deciding which percentage to assign.

The data looks something like this:

Bill-to configuration product family rate
1440 1234 xxx D10
1440 1236 xxx D35
1440 1237 xxx D0K
1440 1239 xxx D56
1608 1920 xxx D0K
54598 3201 xxx D35

Rate is the field I want to fill in based on values in the table below:

FAMILY 1440 1608 54598
D10 10.5% 10.5% 8.6%
D35 10.0% 10.0% 8.2%
D0K 10.0% 10.0% 8.2%
D56 3.0% 3.0% 3.0%

The number accross the percentages represent bill-to codes. Any ideas on how to accomplish this?

 

[ToddH18]

Try sumproduct

Is your data set up like this

As in A1=1440 B1=1234 C1=xxx D1=D10

 

[pagrender]

Using Todd's suggestion, and if TAble 1 is in A1:D6 and table 2 is in G1:J5, put this formula in E1:

=SUMPRODUCT(($G$2:$G$5=D1)*($H$1:$J$1=A1)*($H$2:$J$5))

 

[pagrender]

Oops... had my Table 1 reference off as I forgot the header row... table 1 should be in A1:D7 and you should paste the formula into E2

 

[mitchman7614]

Thanks guys! I've seen that function before, but not like that...much more efficient than my attempts.

Thanks again!

 

[hiker95]

Sorry, wrong post.