Match function in excel

Question

suppose I have this table in excel:

And I want to use match function to find the value 5:

MATCH(5,B2:B5,0)

How I can return both values in B2 and B4

Answer 1

Use TEXTJOIN():

=TEXTJOIN(", ",TRUE,FILTER((ROW($B$2:$B$5)-MIN(ROW($B$2:$B$5))+1),$B$2:$B$5=5,""))

or in Excel 2019

=TEXTJOIN(", ",TRUE,IF($B$2:$B$5=5,(ROW($B$2:$B$5)-MIN(ROW($B$2:$B$5))+1),""))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

---

But my guess is that this is only a step to get what you really want, to return the value in column A where it is 5:

=TEXTJOIN(", ",TRUE,FILTER($A$2:$A$5,$B$2:$B$5=5,""))

2019:

=TEXTJOIN(", ",TRUE,IF($B$2:$B$5=5,$A$2:$A$5,""))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.