3

I have a problem regarding a VLOOKUP formula in Google Sheets. When I use it in Excel or OpenOffice Calc it works like I need but it's somehow different here.

My problem is that I need to find WORD that is amongst other words in some unknown cell in a known column. For example:

C1 contains: "string_6 string_4 string3"
C2 contains: "string_2 string_5 string_1"

And I'm looking for string_5. All I know is that it is somewhere in column C.

How can I find this?

I was trying VLOOKUP because after finding out that C2 contains string_5 I had to relate to D2.

Community
  • 1
  • 1
saint337
  • 33
  • 1
  • 8

1 Answers1

2

You can use wildcards in VLOOKUP - the asterisk will match zero or more characters and the question mark matches exactly one character. So the following should give you what you need:

=VLOOKUP("*string_5*",C1:D10,2,FALSE)

This is the same behaviour as Excel. For more flexibility (allows regular expressions, and can output more than one value), look at QUERY - detailed in Docs help.

aucuparia
  • 2,021
  • 20
  • 27
  • Thank you! That's almost it. Now what if I have "string_5" in a cell? How for example can I make it into "*A1*" ? I can't always type in what I'm looking for it must be dynamic. @aucuparia – saint337 Dec 16 '15 at 18:01
  • I got it now, thank you. I used SUBSTITUTE to create formula with cell adress inbetween **. – saint337 Dec 16 '15 at 18:57