# Vlookup using two criteria

#### NYEXCEL1

##### Board Regular
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>

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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"

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

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

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

here is a sreenshot with same formula

Last edited:
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)

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

=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

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

Replies
11
Views
261
Replies
1
Views
366
Replies
2
Views
297
Replies
1
Views
183
Replies
8
Views
383

1,203,075
Messages
6,053,391
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back