Vlookup all the values matching the search term in

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

vlookup is designed to always give you one result. Either your query matches then it will return the value, else it will return an error. As you want all the values I would recommend a different approach.

1. Create a column next to your column (in your case A:A) and fill it with your condition =IF($A1 = ("*" & B3 & "*", 1, 0). This column should be filled with 0 and 1 depending on the content of your column B.
2. Do whatever you want with the values in column A, and take your newly created column as condition. For example to sum all values in column A that match your condition: =SUMIFS(A:A, B:B, "=1")

I hope this helps to streamline your Excel. If you need further help maybe elaborate on the content of your columns A and B and what you want to achieve with your =vlookup().

Answer 2

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.

Answer 3

Another option is

=FILTER(A2:A, SEARCH(B3, A2:A))

Filters out those values where the string can be found and also supports wildcards within the string or could be extended to match a regular expression instead.