Vlookup using two criteria

[NYEXCEL1]

I have two tables of data as shown below. Since combining as one pivot table is not working, i would like to add a vlookup column on the second table that pulls the revenue number. It seems like the function will have to include both the customer and the period in order to pull the correct revenue number. Please advise and thank you!

Customer	Period	Revenue
Client A	201301	100
Client B	201302	50

<tbody>
</tbody>

Customer	Period	Hours	Time Cost
Client A	201301	10	300
Client A	201302	5	150
Client B	201303	6	200

<tbody>
</tbody>

 

[jamtay317]

I see the customer and the period columns in the data set, but where is the revenue? all I see is "time cost" and "Hours"

 

[NYEXCEL1]

The revenue is in the first table. I need to match the client and period in table 2 with the revenue in table one

 

[danzon]

use SUMPRODUCT

=SUMPRODUCT(--(TABLE!$A$1:$A$500=A1),--(TABLE!B$1:B$500=B1),(TABLE!C$1:C$500))

Where the data to be looked up is in worksheet named TABLE

in summary worksheet A1 and B1 contain criteria to match up

edited - original had error

 

[NYEXCEL1]

Forumla is not working. Should there be a ")" after the second array? and why is the third array not referencing the table?

 

[danzon]

yes, please note that I edited my formula... my original had an error, sorry about that

here is a sreenshot with same formula

 

[NYEXCEL1]

thanks... i am getting a value# error and not sure why. here is my formula..

=SUMPRODUCT(--(Sheet7!$B$1:$B$550=A3),--(Sheet7!$A$1:$A$550=D3),--Sheet7!$C$1:$C$550)

 

[danzon]

see my screenshot above... I think that will show you where your errors are

 

[jamtay317]

=sumproduct(--($b$1:$b$550=a3),--($a$1:$a$550=d3),($c$1:$c$550))
try to take the -- out of the 3rd array and take the sheet names out too

 

[NYEXCEL1]

ok, i will try that...but why take out the sheet names? the data are in two seperate worksheets.