Filter or Index based on cell value and column header

[2da]

Hello,
I want to filter/index values based on specific criteria I specify within a column, but also select a specific column within the range.

Example, Request Table. Users have requested multiple shifts (Column Headers) and I am putting a value R, RA, D, B, x. I want to filter users who are value R, RA, D, B on M-5:10 and so on. I was going to create a tab for each of the days. But copying and changing formulas for multiple tabs is not ideal. So looking to see if there is a formula I can use.

Thanks for your help.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	M - 5:10	M - 10:40	Tu - 5:10	Tu - 10:40	W - 5:10	W - 10:40	Th - 5:10	Th - 10:40	F - 5:10	F - 10:40	Sa - 5:10	Sa - 10:40	Su - 5:10	Su - 10:40
2	User 1	R	B	x	x	x		x	x		x	x		R	R
3	User 2	R	D	x		x	x	R	R	x		x	x	R
4	User 3		x	x	R	x	x	x	x	x	x	x	x	x	B
5	User 4	RA		R				R		R
6	User 5		R		R		R		B		x				R
7	User 6		R		x		x		x		R		x	B	R
8	User 7
9	User 8
10	User 9	x		x	x	R	R	x	x	x	x	x		R
11	User 10	x		R	x	B		x		x		R		R
12
13	M - 5:10
14	User 1	R
15	User 2	R
16	User 4	RA
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:O11	Cell Value	contains "D"	text	YES
B2:O11	Cell Value	contains "B"	text	NO
B2:O11	Cell Value	contains "RA"	text	NO
B2:O11	Cell Value	contains "R"	text	YES
B2:O11	Cell	contains a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:O11	List	x,R,RA,B,D

 

[Peter_SSs]

Welcome to the MrExcel board!

I am not exactly clear what you are wanting as a result. Can you post an example or two (manually completed) of what result(s) you want and explain why that result?

 

[2da]

Hi Peter,

Here is the result i'm expecting. Based on the header, the data is to be returned as follows:

Book3
	A	B	C	D	E
1	M - 5:10		M - 10:40		Tu - 5:10
2	User 1		User 1		User 4
3	User 2		User 2		User 10
4	User 4		User 5
5			User 6
Sheet3

 

[Peter_SSs]

Thanks for the clarification. Try this formula in A2 of Sheet3 (adjusting the ranges if required) and then opy/paste (without further alteration) to C2, E2, ....

=LET(s,FILTER(Data!$B2:$O11,Data!$B1:$O1=A1),FILTER(Data!$A2:$A11,(s<>"x")*(s<>""),""))

2da.xlsm
	A	B	C	D	E	F	G
1	M - 5:10		M - 10:40		Tu - 5:10		Tu - 10:40
2	User 1		User 1		User 4		User 3
3	User 2		User 2		User 10		User 5
4	User 4		User 5
5			User 6
6
Sheet3