formula that returns column headings based on multiple criteria

[Jamel]

Below I'm showing a sample of the spreadsheet I'm working with. I'm looking for a formula that
answers the Answer 2 column. I've already successfully created a formula that answers Answer 1.
In the Answer 1 column I capturing the column heading where the first "A" absence appears.
The true column heading I've highlighted in blue. I included a heading over that row just in case
I need to reference that to compare to the days the student attends class.

Answer 2 is a lot more trickier because I have to honor the days of the week each student attends the class.
In Answer 2 I need to capture the column where the first blank appears after the first "A" absence
while honoring the days of the week the student attends the class. In my table below I've answered
all the Answer 2 to show what needs to be the result.

As always any help with this will be greatly appreciated

not sure why all the grid lines are showing.. there are showing before I hit the post button.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
days of week	Answer 1	Answer 2
Student 1	M W
Student 2	T H
Student 3	H
Student 4	M T W H F
Student 5	W
Student 6	H
Student 7	M W F
Student 8	T
Student 9	M

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"] 3 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"] 5 [/TD]
[TD="align: center"] 8 [/TD]
[TD="align: center"] 9 [/TD]
[TD="align: center"] 10 [/TD]
[TD="align: center"] 11 [/TD]
[TD="align: center"] 12 [/TD]
[TD="align: center"] 15 [/TD]
[TD="align: center"] 16 [/TD]
[TD="align: center"] 17 [/TD]
[TD="align: center"] 18 [/TD]
[TD="align: center"] 19 [/TD]
[TD="align: center"] 22 [/TD]
[TD="align: center"] 23 [/TD]
[TD="align: center"] 24 [/TD]
[TD="align: center"] 25 [/TD]
[TD="align: center"] 26 [/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]

</tbody>

Sheet1

​

 

[Marcelo Branco]

Maybe...

Formula in V3 copied down
=INDEX(C$2:T$2,MATCH(1,INDEX((ISNUMBER(MATCH("*"&C$1:T$1&"*",B3,0)))*(C$2:T$2>U3)*(C3:T3=""),),0))

M.

 

[Jamel]

OMG!

It worked on the sample spreadsheet. Now I need to apply to my real one.
thanks a bunch!

 

[Peter_SSs]

This also seems to do what you want.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		W	H	F	M	T	W	H	F	M	T	W	H	F	M	T	W	H	F
2	days of week	3	4	5	8	9	10	11	12	15	16	17	18	19	22	23	24	25	26	Answer 1	Answer 2
3	M W	A			A		A			A		A					A			3	22
4	T H					A		A			A		A					A		9	23
5	H		A					A												4	18
6	M T W H F						A													10	11
7	W	A					A					A								3	24
8	H		A										A					A		4	11
9	M W F			A	A		A		A	A				A						5	17
10	T					A										A				9	16
11	M				A															8	15
Answer 2

 

[Jamel]

I'm gonna have to research these formulas. Seems like they can be helpful in a lot of work I do. I only use the basic Index/match formula normally. I noticed
in the first answer the isnumber function was included. Also I never seen a formula with the "1" before the Index in the formula. Very interesting. What does that do?

=INDEX(C$2:T$2,MATCH(1,INDEX((ISNUMBER(MATCH("*"&C$1:T$1&"*",B3,0)))*(C$2:T$2>U3)*(C3:T3=""),),0))

I also didn't know you can include wildcards in the formula when you are referencing text. That is really cool.

As for as the aggregate formula, that seems even simpler but I've never used it before. I'm clueless when I'm to use it, but I'm going
to google it to learn more.

Anyway thanks for all the help and I hope you don't mind me asking more about the above formula's so I can use them more on my own.

btw.. Meant to say the grid lines didn't show in my post, but the appeared before I post. I install that add on but I guess I'm
having a bit of trouble with it.

 

[Marcelo Branco]

About MATCH(1,Condition1*Condition2*Condition3,0)
1. This kind of construct is used in formulas to lookup depending on multiple conditions;

2. Each condition generates an array of logical,True/False, values;

3. In Excel the multiplication of logical values (True/False) performs as:
True*True results in 1
True*False results in 0
False*False results in 0;

4. The multiplication of all conditions generates an array of 1s and 0s - in this array the 1s correspond to those rows that meet all conditions (desired result).

5. The MATCH function with the last argument equal 0 (exact match) returns the relative position of the first match;

6. Therefore, for example, MATCH(1,{0;0;1;0,1;0},0) returns 3 since the first 1 corresponds to the third position in the array.

Hope i made myself clear.

M.

 

[Marcelo Branco]

Complementing my post above,
As in your specific case the desired result is first column that meet all conditions, on the sake of clarity, is important to adjust my comments
Number 4 should be
4. The multiplication of all conditions generates an array of 1s and 0s - in this array the 1s correspond to those columns that meet all conditions (desired result).

In number 6 the example should have showed a horizontal array (values separated by commas), that is:
6. Therefore, for example, MATCH(1,{0,0,1,0,1,0},0) returns 3 since the first 1 corresponds to the third position in the array.

M.

 

[Jamel]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		month	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	MAY	MAY	MAY	MAY	MAY	MAY	MAY	MAY	MAY	ABS MONTH	ABS DAY	RETN MONTH	RETN DAY
2	1	day of week	R	F	M	T	W	R	F	M	T	W	R	F	M	T	W	R	F	M	ANSWER	ANSWER	ANSWER	ANSWER
3	2	days of week	18	19	22	23	24	25	26	29	30	1	2	3	6	7	8	9	10	13
4	3	M W					A			A		A			A						APRIL	24	MAY	8
5	4	T R				A		A			A							A			APRIL	23	MAY	2
6	5	R						A					A								APRIL	25	MAY	9
7	6	M T W R F						A	A	A	A	A				A			A		APRIL	25	MAY	2
8	7	W					A					A									APRIL	24	MAY	8
9	8	R	A					A					A								APRIL	18	MAY	9
10	9	M W F			A		A		A	A		A		A	A		A				APRIL	22	MAY	10
11	10	T									A										APRIL	30	MAY	7
Sheet1

 

[Jamel]

Sorry but I got booted out of the forum before I could finish my post

 

[Jamel]

Sorry but I got kick out of the forum before I could finish my post. I'm going to send another one shortly