How to find the row index of a particular value from a table using Excel VBA-CodePudding

A 1 2 3 
B 4 5 6
C 7 8 9

From the above data, I want to return the row header value for a particular value. for example, if the value is 5 it should return B.

I tried something like this

=INDEX(A1:A3,MATCH(C3,B1:D3,0),1)

but it cant be applied in the case of multiple columns.

Thank you for the help.

CodePudding user response：

Can try-

=INDEX(A1:A3,MAX(ROW(A1:A3)*(B1:D3=F1)))

CodePudding user response：

Assuming you just have a single value that would equal your search-value:

=INDEX(A1:A3,SUMPRODUCT((B1:D3=5)*(ROW(B1:D3))))

If you have Microsoft365, you could try:

=CONCAT(IF(B1:D3=5,A1:A3,""))