Lookup based on two criteria

[anuradhagrewal]

Hello
I have a problem.
I have two tables in Sheet 1 and Sheet 2 of a workbook.
There are 3 fields in each of the tables out of which Name and Date are common in both the worksheets with varying data.
Now I want to function which matches the name and date with the hours worked on that date by that person.
For eg : WDAU (A2) on 6-Dec-20 (B2) sold 22 units (C3) then on the given date how many hours did he work which is to be picked from sheet 2.
The same name can sell on different dates different units and work for different hours.
This all needs to be matched into a single table.
I have tried Vlookup(A2&B2 sheet2!A2:C40,3,false) but it return #N/A.
Can anybody please help. As always it will be much appreciated.

Thanks in advance

Regards

Anu

 

[Toadstool]

Hi Anuradhagrewal,

To use VLOOKUP you'd need to add a worker column with the table_array as a concatenated Name and Date from the searched on sheet and the lookup_value as the concatenated Name and Date as you have shown.

You could also do this using INDEX/MATCH/INDEX or INDEX/AGGREGATE without using a worker column.

It would be best if you could upload your data with a worked example using XL2BB, or even as a text table, so people don't have to type lots of test data.

 

[anuradhagrewal]

Thanks for your reply....
But i would much appreciate if you can share with me the exact formula for the one mentioned.
Once again thank u for your time.

 

[Toadstool]

I'm not going to type your data so here's a sample using my test data:

Anuradhagrewal.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Date	Units Sold	Hours Worked			Worker Column	Name	Date	Hours Worked
2	Fred	1/1/2020	7	7:12			Fred43831	Fred	1/1/2020	7:12
3	Fred	1/2/2020	4	8:55			Jim43831	Jim	1/1/2020	8:47
4	Fred	1/4/2020	3	9:44			Fred43832	Fred	1/2/2020	8:55
5	Fred	1/6/2020	22	7:22			Jim43833	Jim	1/3/2020	9:36
6	Jim	1/1/2020	3	8:47			Fred43834	Fred	1/4/2020	9:44
7	Jim	1/3/2020	8	9:36			Jim43835	Jim	1/5/2020	8:12
8	Jim	1/5/2020	1	8:12			Fred43836	Fred	1/6/2020	7:22
9	Jim	1/6/2020	2	9:23			Jim43836	Jim	1/6/2020	9:23
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=VLOOKUP(A2&B2,$G$2:$J$9999,4,0)
G2:G9	G2	=H2&I2

 

[anuradhagrewal]

You r a darling....
Thanks a ton
God bless

 

[mikerickson]

In this case there's no need for VLOOKUP, something like this should work

=SUMIFS(Sheet2!C:C, Sheet2!A:A, "WDAU", Sheet2!B:B, DateValue("6-14-2020"))