INDEX/MATCH output all matches ?

[JumboCactuar]

Hi
i have the following formula

=INDEX($F$28:$F$1000,MATCH(H9,$H$28:$H$1000,0))

which finds match in H28:H1000 and outputs corrosponding value in F Column

However if there are multiple matches of H9, it always outputs first value
Is there a way to output them all ?

Array needed i think

Any help appreciated

 

[RasGhul]

Hi Jumbo,

Try

=IFERROR(INDEX($F$28:$F$1000,SMALL(IF($H$28:$H$1000=$H$9,ROW($F$28:$F$1000)-ROW($F$28)+1),ROWS($J$28:J28))),"")

Needs to be entered with CTRL+SHIFT+ENTER (not just enter)

Note that this section ROWS($J$28:J28) needs to match where you start this formula on your sheet.