Cross referencing cells based on text?

[tbush33]

Hello,

I am trying to build a schedule and I have names in Column A and their shift assignments populated in the rest of the columns. What I would like to do is have a row below that table that will tell me which employee will be working AM and which employee would be working PM on each day. Here is an example of what I would like:

Employee	Jan 1st	Jan 2nd	Jan 3rd
Alex	AM	AM	OFF
Brenda	PM	PM	OFF
Susie	OFF	OFF	AM
Bob	OFF	OFF	PM
AM	Alex	Alex	Susie
PM	Brenda	Brenda	Bob

<tbody>
</tbody>

In the table above, I have simulated what I would like to have displayed. For the sake of explaining, we will say that the "Employee" cell is A1. So, in cells B6:D6, I would like it to reference the cells above and find which cell has the text "AM", then I would like it to find which employee is associated to that cell and display it down below. In the example above, on Jan 1st Alex works the AM shift. Excel sees the AM keyword and then displays the employee name in cell B6. I would do this manually, but with employees calling out and taking days off, it constantly changes.

If anyone could help assist me in this, I would appreciate it.

Thanks,
Travis

 

[arunsjain]

Hi,

Try Index and Match function for this like below:

Employee	Jan 1st	Jan 2nd	Jan 3rd
Alex	AM	AM	OFF
Brenda	PM	PM	OFF
Susie	OFF	OFF	AM
Bob	OFF	OFF	PM
AM	=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,B$2:B$5,0)),"")	=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,C$2:C$5,0)),"")	=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,D$2:D$5,0)),"")
PM	=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,B$2:B$5,0)),"")	=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,C$2:C$5,0)),"")	=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,D$2:D$5,0)),"")

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

Hope it will work for you.

Cheers !!!