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
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
