Match/Index/Lookups?

[SwiftM]

Hi Excel Gurus,

Need a little help which i have been scratching my tiny brain over!! Any help would be very much appreciated!!

As you can see above, I have the days and dates of a month on a sheet called "April". What I want to achieve on a different sheet called "Employees" is a table where when I load up the workbook it populates which employee is working a D, L, or N shift. Heres the table which i want to populate automatically:

So basically in cell H3 for example, I would ideally like it to look up the date above in Row 2 which is the 7th, then find the 7th in the worksheet "April" and find the employee who is working a D on that date from looking in column A (employees). This would be the case for all the cells so some sort of VBA coding I imagine would be best?

How would this be done as I genuinely have no idea?

Many thanks for your help.
M

 

[dermie_72]

I used the table you provided and named the sheets April and Employees.In Cells B3:AE8 enter the following formula into B3 and press control Enter.

=IFERROR(INDEX(April!$A16:$A$20,MATCH(Employees!$A3,April!C16:C$20,0)),"")

Or enter it into B3 and drag across and drag down to row 8
The index needs to encompass all the employees so adjust your range for the real data.
When it came to L in row 10 I changed the formula to :

=IFERROR(INDEX(April!$A16:$A$20,MATCH(Employees!$A10,April!C16:C$20,0)),"")

See if that works.

 

[SwiftM]

I used the table you provided and named the sheets April and Employees.In Cells B3:AE8 enter the following formula into B3 and press control Enter.

=IFERROR(INDEX(April!$A16:$A$20,MATCH(Employees!$A3,April!C16:C$20,0)),"")

Or enter it into B3 and drag across and drag down to row 8
The index needs to encompass all the employees so adjust your range for the real data.
When it came to L in row 10 I changed the formula to :

=IFERROR(INDEX(April!$A16:$A$20,MATCH(Employees!$A10,April!C16:C$20,0)),"")

See if that works.

Hi Dermie,

Thank you for the reply and for your help!!

I have inserted the formulas you provided but the value returned is "0" and not the name of the employee working a D or L. i.e. Employee 1, Employee 2 etc

Here's a small snippet of the D table result:

Can you suggest what the issue may be?

Cheers,
M

 

[dermie_72]

please show the formula in your snippet as well as the location of the original data

 

[SwiftM]

please show the formula in your snippet as well as the location of the original data

HI Dermie,

Please see below,

Pulls the data from the April sheet (below)

Many thanks for your help
M

 

[dermie_72]

Sorry I've been away. Checking the data. It looks like you're looking in Column A for the Index, where the employees name is in Column B.
So the formula was returning a blank cell which equates to 0. Checnhe that to $B16:$B$20 and adjust the numbers for each range and it should work.