Index/Return cell references from table array based on "Yes"/"No" response

I would like a formula that returns the column and row name, based on "Yes"/"No" response in a table array. Please see below. Basically, I would like a formula that automatically returns the values in the last row below (excel attached):

SCENARIO	A	B	C	D	E
1	Yes	No	No	No	No
2	No	No	No	Yes	No
3	No	Yes	Yes	No	No
4	No	No	No	No	Yes
5	No	No	No	No	No
	Return the following scenarios, based on above "Yes" response
Scenarios	1A	2D	3B	3C	4E

Just to be clear,

You want to look in a range for the word "Yes" and if found return: row number and column letter formatted the way you have above, "1A" for example?

Yes, exactly that.

Does it have to look at your headers or do you want it to look at excels cell references?

Lets get the ball rolling then

Try putting this in range B9

=CONCATENATE(ROW(INDIRECT(CELL("address",INDEX(B2:B6,MATCH("Yes",B2:B6,0))) )),SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(CELL("address",INDEX(B2:B6,MATCH("Y es",B2:B6,0))))),4),1,""))

Hope this helps

So I get an N/A response with the formula you suggested. File attached.

If you look at the second "Yes" in the formula there is a space that should not be there.

It looks like "Y es", remove that space and all should be fine.

Awesome. So the formula works, but I would like it to look at the headers, and not the cell references.

Try this in B9:

=B1&INDIRECT("A"&MATCH("Yes",B2:B6,0)+1)

Hope this helps

Awesome. This works. But there's a possibility of the table containing up to 10 "Yes" responses. Is it possible to return the headers for 10 responses? Excel sheet with example attached.

How about this:

Attachment 21400

This would be perfect, except I need each "header" to be in a separate cell, as they will be referenced elsewhere.

Using a Sub and not a Function

Code:

---

Option Explicit

Sub drv()
    Call Yes(ActiveSheet.Range("A1:F6"), ActiveSheet.Range("A10"))
End Sub

Sub Yes(YesRange As Range, OutputRange As Range)
    Dim r As Long, c As Long
    Dim A As Variant
    Dim s As String
   
    A = YesRange.Value
   
    For r = 2 To UBound(A, 1)
        For c = 2 To UBound(A, 2)
            If UCase(Trim(A(r, c))) = "YES" Then
                s = s & A(r, 1) & A(1, c) & ","
            End If
        Next c
    Next r
   
    A = Split(s, ",")
   
    OutputRange.Cells(1, 1).Resize(1, UBound(A)).Value = A
   
End Sub

---

cool,

I was working on a sub that placed them transposed compared to yours Paul.

Paul's code will be the best route forward for you i would imagine but i will paste below what i was working on for reference only.

Code:

---

Sub GetRef()    Dim colRange As Range, cCell As Range, rCell As Range, x As Long
   
    Set colRange = Range("B1:F1").Cells ' range of headers
   
    For Each cCell In colRange.Cells
        x = 9 ' row you want results to start from
        For Each rCell In Range(Cells(cCell.Offset(1, 0).Row, cCell.Column), Cells(cCell.End(xlDown).Row, cCell.Column))
            If UCase(rCell.Value) = "YES" Then
                Cells(x, cCell.Column).Value = Cells(rCell.Row, 1).Value & Cells(1, rCell.Column).Value
                x = x + 1
            End If
        Next rCell
    Next cCell
End Sub

---

Something to note: my code assumes there are no blank cells in your data as it uses the end function.


Hope this helps

Awesome! What a genius! Thank you very much!