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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,521
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,129,466
Messages
5,636,440
Members
416,919
Latest member
twc2c

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