Find Nth occurrence horizontally, then return appropriate column header?

[trikky]

Hi all!

I've searched and searched, without finding exactly what I'm looking for. My goal in this exercise is to look horizontally across an array, find the Nth occurrence of a specific text string ("No") and then, in the appropriate cell, return a value that is the column header for the column in which that string occurred. In the next cell on the same row, it would return the next occurrence's column header.

Here's how my sheet is laid out. Highlights are there for demonstration purposes only, but the pinkish area contains the cells in which I want the magic to occur.

Row 1 is dedicated to column headers, which are actually names of elements users are being evaluated upon ("did they do this?"). Rows 2-6 are the answers for each user (the user name is entered in column A). So Column A is the list of names, Columns B-K are the answers (yes / no / N/A)

The sample below gives a visual. The red text (in First Instance/Second Instance columns) show what I want to appear. The black text a couple of rows down shows the result from my testing (explained next paragraph).

During my time trying to figure it out myself, a basic index Match =INDEX($B$1:$E$1,MATCH("No",B4:E4,0) in F4 returned 'Question 1', but an attempt to expand upon that in F5 {=INDEX($B$1:$E$1,SMALL(IF($B4:$E4="No",COLUMN($B4:$E4)),2))} returned 'Question 3', which is of course is incorrect.

Thank you for any assistance you can provide.

	A	B	C	D	E	F	G	H	I	J
1	Agent Name	Question 1	Question 2	Question 3	Question 4	First Instance	Second Instance	Third Instance	4th Instance	5th Instance
2	Bill	Yes	Yes	No	No	Question 3	Question 4
3	Ben	Yes	Yes	Yes	Yes
4	Mary	No	No	Yes	Yes	Question 1	Question 3
5	Betty	Yes	Yes	Yes	Yes
6	Sonya	No	No	Yes	Yes	Question 1	Question 2

<colgroup><col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;" span="10"> <tbody> </tbody>

 

[Eric W]

Welcome to the forum.

Put this formula in F2:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF($B2:$E2="No",COLUMN($B2:$E2)-COLUMN($B2)+1),COLUMNS($F2:F2))),"")

and confirm it by pressing Control+Shift+Enter, not just enter. Then drag it down and to the right as needed.

 

[trikky]

Welcome to the forum.

Thanks.

Put this formula in F2:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF($B2:$E2="No",COLUMN($B2:$E2)-COLUMN($B2)+1),COLUMNS($F2:F2))),"")

and confirm it by pressing Control+Shift+Enter, not just enter. Then drag it down and to the right as needed.

You are the sunshine of my life. I put it in my actual worksheet and it works perfectly (not that I had any doubt, of course).

Thank you very much for the solution. This one was making my pull my hair out, and according to my kids, I don't have a lot of hair left.

 

[Eric W]

Glad I could help!

(And I'm rapidly running short of hair myself!)

 

[Jppatter]

Hey everyone,
I’m new to the forum.

rather than create a new thread I wanted to reply here as I wanted to expand on this original question, I know it’s a very old thread.

i want to accomplish the same thing as above, but is it possible to return values based on two criteria? I have attached a photo of what I am trying to accomplish as well as the code I have put into the cell I am having trouble with. I took the original code that was suggested here and added and AND function, but I figured that this may not work as it may need some other adjustments.

I am building a report that shows which days materials drop below 0 and each consecutive day that they go further down, but only on days that have a material usage and not the negative days in-between that materials aren’t needed.

Thanks,
Justin

 

[Joe4]

Hey everyone,
I’m new to the forum.

rather than create a new thread I wanted to reply here as I wanted to expand on this original question, I know it’s a very old thread.

Re-posted here: Return nth value based on two criteria
Please post all replies there.

(And for future reference, please note rule 12 here: Message Board Rules).