Multiple Criteria Lookup

CF64

New Member
Joined
Feb 17, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 17, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1​
Employee IDPaid day off #1Paid day off #2Paid 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
ABC
1​
Employee IDPaid day off datePaid 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

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Worksheet #1
2Employee IDPaid day off #1Paid day off #2Paid day off #3
31101/01/202001/02/202001/03/2020
41202/03/2020  
51302/04/2020  
6
7Worksheet #2
8Employee IDPaid day off datePaid day off occurrence
91101/01/20201
101202/03/20201
111302/04/20201
121101/02/20202
131502/05/20201
141602/06/20201
151101/03/20203
161801/08/20201
Sheet7
Cell Formulas
RangeFormula
B3:D5B3=IFERROR(AGGREGATE(15,6,$B$9:$B$16/($A$9:$A$16=$A3),COLUMNS($B3:B3)),"")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,427
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=TRANSPOSE(FILTER(Sheet2!B2:B100,Sheet2!A2:A100=A2,"None"))
 

CF64

New Member
Joined
Feb 17, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,427
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which formula are you talking about?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 17, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 17, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Both of your input is greatly appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,129,315
Messages
5,635,512
Members
416,862
Latest member
MGDlite

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top