How to extract a column value based on value in the range

[venkatvedavyasan]

I have a team ROTA in excel sheet. The first column is the Name and first row is the date. The intervening cells are loaded with values.

Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
Day	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Person 1	S	P	2	2	2	2	L	2	2	2	2	2	2	M	2	2	2	2	2	2	2	P	S	2	2	2	2	2	2	2
Person 2	2	2	2	2	2	2	L	2	2	L	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	P	S
Person 3	2	2	2	2	2	2	2	2	2	2	2	2	2	N	S	P	2	2	2	2	N	2	2	2	2	2	2	2	2	2
Person 4	2	2	2	2	2	2	2	P	S	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
Person 5	2	2	2	2	2	2	2	S	P	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2

I'm trying to find the dates for a particular person with particular value.

Please guide with a formular through which I can achieve this.

	S	P	L	M	N
Person 1	1	2	7	14
Person 1	23	22
Person 2			7
Person 2	30	29	10
Person 3	15	16			14
Person 3					21
Person 4	9	8
Person 5	8	9

[#2 · Accepted solution]

@venkatvedavyasan 

See the attached workbook.

[#3]

@HansVogelaar 

How did you get the Spill getting filled automatically ? Because when I copy person 1 to 5 in from A10-A14 I'm not getting the value as you got.

Not looking same as what you have got ?

How to achieve this ? 

[#4]

@venkatvedavyasan

In entered the formula in B10, then filled it to the right to F10.

I copied B10:F10, then pasted to B12. B14, B16 and B18.

[#5]

@venkatvedavyasan 

=IFNA(REDUCE(HSTACK("","S","P","L","M","N"),A3:A7,LAMBDA(a,b,VSTACK(a,REDUCE(b,HSTACK("S","P","L","M","N"),LAMBDA(u,v,HSTACK(u,IFERROR(TOCOL(FILTER(B1:AE1,FILTER(B3:AE7,A3:A7=b)=v)),""))))))),"")

With Office 365 or Excel for the web this formula could be an alternative.