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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Jan 1st[/TD]
[TD]Jan 2nd[/TD]
[TD]Jan 3rd[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]AM[/TD]
[TD]AM[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Brenda[/TD]
[TD]PM[/TD]
[TD]PM[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Susie[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Susie[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]Brenda[/TD]
[TD]Brenda[/TD]
[TD]Bob[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 1214"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Jan 1st[/TD]
[TD]Jan 2nd[/TD]
[TD]Jan 3rd[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]AM[/TD]
[TD]AM[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Brenda[/TD]
[TD]PM[/TD]
[TD]PM[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Susie[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,B$2:B$5,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,C$2:C$5,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$6,D$2:D$5,0)),"")[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,B$2:B$5,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,C$2:C$5,0)),"")[/TD]
[TD]=IFERROR(INDEX($A$2:$A$5,MATCH($A$7,D$2:D$5,0)),"")[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"></colgroup>[/TABLE]





Hope it will work for you.

Cheers !!!