VLOOKUP to return MULTIPLE corresponding values

=INDEX($B$2:$B$9, SMALL(IF($A$11=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), COLUMN(A1)))

After typing formula, using Ctrl + Shift + Enter instead of just Enter.

Then fill formula into the other cells in column/row until #NUM! appears. If you want to return multiple corresponding values horizontally, use COLUMN(), other use ROW()

Collected from extendoffice for personal use only