Vertical then Horizontal MATCH lookup array formula

[jdm917]

Greetings,

I am hoping someone in this forum can help me with this as I am stumped. I need to write an array formula that first evaluates against a vertical column of values and then finds a different value in the row of that vertical value. This sounds simple to do with INDEX - MATCH, but I actually need the formula to return the column reference after the horizontal lookup AND to add a little more complexity, the horizontal value I'm looking for can appear multiple times in the same row, so I need the formula to return all results. Here's a visual example, hopefully it clarifies what I'm talking about:

Column 1	Column 2	Column 3	Column 4
AAA	X	Y	Z
BBB	X	Y	X
CCC	Y	Y	Z

<tbody>
</tbody>

Vertical Lookup: BBB
Horizontal Lookup: X
Need the formula to return: 2 and 4

Hopefully, that explains what I need, thank you for your help!

 

[Aladin Akyurek]

Let A1:D3 house the data.

A5: BBB
A6: X

In A7 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(INDEX($A$1:$D$3,MATCH(A$5,$A$1:$A$3,0),0)=A$6,
    COLUMN($A$1:$D$3)-COLUMN($A$1)+1),ROWS(A$7:A7)),"")

 

[jdm917]

Let A1:D3 house the data.

A5: BBB
A6: X

In A7 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(INDEX($A$1:$D$3,MATCH(A$5,$A$1:$A$3,0),0)=A$6,
    COLUMN($A$1:$D$3)-COLUMN($A$1)+1),ROWS(A$7:A7)),"")

Thank you, sir! That works perfectly!

 

[Aladin Akyurek]

Thank you, sir! That works perfectly!

You are welcome.