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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
</tbody>[/TABLE]

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.