Lookup/Match/Index

Hello,

I have a document with two sheets. One sheet will contain a daily schedule, and the other will have a monthly schedule. I need to be able to reference the second sheet and insert the data into the appropriate cells on the first sheet when the date at the top of the page is manually changed.

The data sheet looks like this:

(Row 1) Employee # | Name | Unique combination of ID and date | Date | Unique combination of ID and date | Date | Unique combination of ID and date | Date | (so on for the entire month)

What I want is a formula on sheet 1 that will combine the date and employee numbers already listed on the sheet to create a unique number, then reference the second sheet for that number, and return the start time from one cell, and the end time from another.


I can't use vlookup or hlookup because of the way the document is set up, the unique numbers are in every third column and cant be placed in 1 row or column.

What I just tried:

=INDEX(Hours!$F$6:$CQ$99,MATCH($I7&$J$3,Hours!$C6:$CQ6,0),MATCH("end",Hours!$C$5:$CQ$5,0))

This works for the first shift- which is located in c6, but when the date is changed on sheet 1 the formula is returning N/A and not locating the unique number ($I7&$J$3)... I am stumped.

Any help would be GREATLY appreciated.

It would be very useful if you could post an example workbook, so that we can see the layout of your data.

Originally Posted by Andrew-R

It would be very useful if you could post an example workbook, so that we can see the layout of your data.

This is my first post on the sight, what is the easiest way to do that?

Oops, I meant ***site***

I have uploaded a simplified example of what I need. I only used 1 sheet (let the borders represent the two sheets)