Need help applying an INDEX formula

[Unexist]

I would like to transform the table below:

[TABLE="width: 358"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Events[/TD]
[TD]Location[/TD]
[TD]coach[/TD]
[TD]cleaning[/TD]
[/TR]
[TR]
[TD]1-jan[/TD]
[TD]A[/TD]
[TD]John[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2-jan[/TD]
[TD]B[/TD]
[TD]Mike[/TD]
[TD]Suzan[/TD]
[/TR]
[TR]
[TD]2-jan[/TD]
[TD]C[/TD]
[TD]Nick[/TD]
[TD]Jenny[/TD]
[/TR]
[TR]
[TD]3-jan[/TD]
[TD]D[/TD]
[TD]Ralph[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4-jan[/TD]
[TD]E[/TD]
[TD]Nick[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]4-jan[/TD]
[TD]F[/TD]
[TD]John[/TD]
[TD]Suzan[/TD]
[/TR]
</tbody>[/TABLE]


To the following table:


[TABLE="width: 450"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]John[/TD]
[TD]Mike[/TD]
[TD]Nick[/TD]
[TD]Jenny[/TD]
[TD]Ralph[/TD]
[TD]Suzan[/TD]
[/TR]
[TR]
[TD]1-jan[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-jan[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3-jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-jan[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]




So in summary: I want to look up the date and see who is scheduled for duty on that day. There can be multiple events on one day. And there are multiple columns (3) for different employee-roles/functions.

I've implemented the following VLOOKUP formula only to realize it stops counting after the first LOOKUPVALUE was found:

=IFERROR(IF(VLOOKUP($A3;Planning!$G:$I;3;FALSE)=B$1;"x";"")&IF(VLOOKUP($A3;Planning!$G:$J;4;FALSE)=B$1;"x";"");"")

Where A3 = date.

Hope this makes sense...

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F	G
1	Events	Location	coach	cleaning
2	01-Jan	A	John
3	02-Jan	B	Mike	Suzan
4	02-Jan	C	Nick	Jenny
5	03-Jan	D	Ralph
6	04-Jan	E	Nick	Mike
7	04-Jan	F	John	Suzan
8
9
10
11	To the following table:
12
13
14		John	Mike	Nick	Jenny	Ralph	Suzan
15	01-Jan	x
16	02-Jan		x	x	x		x
17	03-Jan					x
18	04-Jan	x	x	x			x
17

Cell Formulas
Range	Formula
B15	=IF(SUMPRODUCT(ISNUMBER(SEARCH($A15,$A$2:$A$7)) * ISNUMBER(SEARCH(B$14,$C$2:$D$7)))>0,"x","")