excel - Vlookup all the values matching the search term instead of just one.

Question

So the following formula returns only the first value from the range. I kinda need all the matching search results indexed instead of just 1.

=Vlookup("*" & B3 & "*",A:A,1,0)

SPREADSHEET LINK

Answer 1

With Google Sheets use Query:

=QUERY(A:A,"select A where A contains """ & B3 &"""")

---

Since you have the Excel tag use this formula for excel:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:INDEX(A:A,MATCH("ZZZ",A:A)))/(ISNUMBER(SEARCH($B$3,$A$2:INDEX(A:A,MATCH("ZZZ",A:A))))),ROW(1:1))),"")

Copy/drag it down sufficient for your needs.