Help with a formula for populating a rota with names

[alexleesonmill]

Hi there,

I'm looking for some help with the attached rota.

Basically, what i'm looking for is help with a formula to populate a rota and make things a bit easier to manage.

Say for example on the 'Rota' sheet, if I was looking at B9, I would like to work out how i could get the spreadsheet to look on the 'Hours' spreadsheet for the a 2-10.30 shift in the B column of that sheet, and then when it finds 2-10.30, it shows the persons name from the A column on the 'Rota' Sheet.

So for example on the document I attached, on the hours sheet, it would look down the whole B column, find 2-10.30 in B11, and then would display the value of A11, in this case 'Bill'.

Does that make sense? I'm fairly new to excel, i'm been trying things like VLOOKUP and MATCH but can't seem to get anything to work.

Any help would be most appreciated, although i'm not even sure if what i'm asking is possible!

Thanks

[DannyJ]

VLookup cannot look left which is the issue yo uare having.

Put this in B9

=INDEX(Hours!$A$3:$A$62,MATCH(C9,Hours!$B$3:$B$62,0))

[JohnTopley]

I think we look in column C and put answer in B based on your file

in B3

=IFERROR(INDEX(Hours!$A$3:$A$58,MATCH(Rota!$C3,INDEX(Hours!$B$3:$H$58,0,MATCH(Rota!B$2,Hours!$B$1:$H$1,0)),0)),"")


Copy down

Highlighted references need to be amended for other days/times

Attached saved as xlsx file

[samba_ravi]

b9=IFERROR(INDEX(Hours!$A:$A,MATCH(C9,INDEX(Hours!$1:$65536,,MATCH(B$2,Hours!$1:$1,0)),0)),"")

The cell format is in Text, Change the cell format to general

[alexleesonmill]

Hi,

Thanks so much for all the help, it's hugely appreciated.

Only other issue i'm now facing is that when there are multiple people doing the same shift on a single day.

For example two people on Tuesday morning working 7.30-2.30, it just pulls through the box I input first for both shifts.

Is there any way around this?

Thanks,

Alex

[JohnTopley]

Try

In B3

=IFERROR(INDEX(Hours!$A$3:$A$62,SMALL(IF(Hours!$B$3:$B$62=$C3,ROW(Hours!$A$3:$A$62)-ROW($A$3)+1,""),COUNTIFS($C$3:C3,C3))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down to row 6

Select B3:B6, copy and paste to B9:B12

All ranges need adjusting for each day.