Multiple Criteria Lookup

[CF64]

Hello,
I have a workbook with 2 worksheets.
Between the 2 worksheets there is one common column which contains employee IDs.
Worksheet 1 contains an employee ID (column A), Paid day off occurrence # x (column B), Date of paid day off occurrence x (column C)
Worksheet 2 contains an employee IDs and these employee IDs (column A) appear on multiple rows , each employee row lists each paid day off (column B), and I created a column to display the paid day off occurrence (column C) (if it was the first day off they took in a year, a 1 is displayed, if it was the second day off in a year, at 2 is displayed, and so on)

On a separate worksheet in the same workbook, I have a column with employee IDs which contains all sorts of additional information. What I would like to do is create a column that looks at the ID in the same row, then goes to the other worksheet, finds that ID and based on additional criteria such as which paid day off occurrence (1, 2, 3, etc), returns the date of that occurrence.

I've tried using an index/match formula and a formula which used "CHOOSE" - =VLOOKUP(A13&A14&A15,CHOOSE({1,2},A2:A10&B2:B10&C2:C10,D2:D10),2,0)

However, in both cases, I keep getting an #N/A error. Is it just not possible to do this type of array formula lookup between 2 different worksheets? Any suggestions/solutions are greatly appreciated.

Thank you

 

[jasonb75]

It's not (in my opinion) the best way, but based on the description you give, it should work if the ranges are correct and there is a matching row for the criteria.

Couple of observations.
Your description refers to columns A, B, and C on both sheets, yet your formula refers to 3 cells in column A as the criteria.
Your formula doesn't refer to a second sheet, everything is looking at the sheet with the formula.
Finally (if you're filling the formula down to multiple rows) the lookup ranges need to be absolute.

 

[CF64]

It's not (in my opinion) the best way, but based on the description you give, it should work if the ranges are correct and there is a matching row for the criteria.

Couple of observations.
Your description refers to columns A, B, and C on both sheets, yet your formula refers to 3 cells in column A as the criteria.
Your formula doesn't refer to a second sheet, everything is looking at the sheet with the formula.
Finally (if you're filling the formula down to multiple rows) the lookup ranges need to be absolute.

Thank you for your reply and apologies for my incomplete description.

What I was trying to do was, with a formula in columns B, C, and D of worksheet 1:
- look up in worksheet 2, the Employee ID from worksheet 1 column A and then return the date which corresponded to the first occurrence of a paid time off from worksheet 2 into column B of worksheet 1, then the second paid day off occurrence from worksheet 2 in column C, and so on.

For Employee ID 11, from worksheet 2, the first paid day off is 1/1, the second is 1/2, the third is 1/3, and these dates would display in columns B, C, and D of Worksheet 1 respectively

	Worksheet #1
	A	B	C	D
1​	Employee ID	Paid day off #1	Paid day off #2	Paid day off #3
2​	11​	1/1/2020​	1/2/2020​	1/3/2020​
3​	12​	2/3/2020​
4​	13​	2/4/2020​
	Worksheet #2
	A	B	C
1​	Employee ID	Paid day off date	Paid day off occurrence
2​	11​	1/1/2020​	1​
3​	12​	2/3/2020​	1​
4​	13​	2/4/2020​	1​
5​	11​	1/2/2020​	2​
6​	15​	2/5/2020​	1​
7​	16​	2/6/2020​	1​
8​	11​	1/3/2020​	3​
9​	18​	1/8/2020​	1​

I am definitely open to any and all (non VBA) suggestions for how to accomplish this more effectively/efficiently as well.

Thank you

 

[jasonb75]

That's slightly different to how I had interpreted the description, looking at the captures I don't see any obvious way to do it with vlookup. I've shown an alternative method below, although it is on one sheet in the example it will work just as well with the lower table in a second sheet.

sumif weeknum.xlsx
	A	B	C	D
1	Worksheet #1
2	Employee ID	Paid day off #1	Paid day off #2	Paid day off #3
3	11	01/01/2020	01/02/2020	01/03/2020
4	12	02/03/2020
5	13	02/04/2020
6
7	Worksheet #2
8	Employee ID	Paid day off date	Paid day off occurrence
9	11	01/01/2020	1
10	12	02/03/2020	1
11	13	02/04/2020	1
12	11	01/02/2020	2
13	15	02/05/2020	1
14	16	02/06/2020	1
15	11	01/03/2020	3
16	18	01/08/2020	1
Sheet7

Cell Formulas
Range	Formula
B3:D5	B3	=IFERROR(AGGREGATE(15,6,$B$9:$B$16/($A$9:$A$16=$A3),COLUMNS($B3:B3)),"")

 

[Fluff]

How about

=TRANSPOSE(FILTER(Sheet2!B2:B100,Sheet2!A2:A100=A2,"None"))

 

[CF64]

Thank you for the reply. How do I modify the formula to get occurrence #2's date in cell C3 since there are the 2 conditions (employee ID and the specific occurrence)?
Thank you

 

[Fluff]

Which formula are you talking about?

 

[jasonb75]

You shouldn't need to modify either for that, my formula uses the COLUMNS() part as a counter, @Fluff's will return the dates by employee in the order that they are listed in sheet 2.

 

[CF64]

You shouldn't need to modify either for that, my formula uses the COLUMNS() part as a counter, @Fluff's will return the dates by employee in the order that they are listed in sheet 2.

Thank you. It worked! This was super helpful. Very much appreciated

 

[CF64]

Both of your input is greatly appreciated