-2

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19

3 Answers3

4

With Google Sheets use Query:

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

enter image description here

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.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Is it possible to make it case-insenstive with query? – rockinfreakshow Apr 25 '17 at 15:21
  • Query is case sensitive. Make sure your data has more than one entry with that search item. `jea` only has one match in the list you provided. – Scott Craner Apr 25 '17 at 15:24
  • @rockinfreakshow try this: `=QUERY(A:A,"select A where upper(A) contains upper(""" & B3 &""")")` – Scott Craner Apr 25 '17 at 16:55
  • =QUERY(A:B,"select A,B where LOWER(A) contains """ & C3 &"""" OR LOWER(B) contains """ & C3 &"""")") This is wrong but what needs to be fixed if I want to search the query term in both A and B columns – rockinfreakshow Apr 25 '17 at 17:38
  • 1
    That is a new question @rockinfreakshow . This question only dealt with the single column. Mark one of the answers as correct and ask a new question. – Scott Craner Apr 25 '17 at 17:45
  • here's some helpful documentation on the query language for the gsheet function: https://developers.google.com/chart/interactive/docs/querylanguage#where – grantr Aug 30 '23 at 21:09
2

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.

Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
0

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().

larsl
  • 338
  • 1
  • 10