Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10003
  • Last Modified:

Excel find text within cell in a range of cells.

I can't find an answer to my exact situation here or elsewhere.  It's pretty simple, here's an example:

(row 1 and col 1 are the spreadsheet row/column)

   A                         B
1 bird                     one
2 bird dog             two      
3
4 (text to lookup) (VLOOKUP function)


I would like to use a vlookup function in cell B4 to find text in cell A4 within the range A1:B2 and report the value in column B. For example if A4 contains the value "bird", and I enter in B4 VLOOKUP(A4,A1:B2,2,0) and it returns the value I need - in B1 "one". But if I enter VLOOKUP("dog",A1:B2,2,0), it returns the #N/A error instead what I need, which is the value in B2 "two". I need the latter function to report the value in B2 = "two". In other words, find the text string WITHIN the cells in column A, not just the matching value. Is there a formula that will get me there or does it have to involve a macro? I much prefer a formula...
0
orerockon
Asked:
orerockon
1 Solution
 
NBVCCommented:
You can use wildcard character * with VLOOKUP

e.g.

in B4:

=VLOOKUP("*"&A4&"*",A1:B2,2,0)
0
 
Guru JiCommented:
You just put your cursor at the end of comma after two and you will see two options

One is TRUE Approximate Match and other is False (Exact Match).

Using this, you will get what you want.

=VLOOKUP(A4,A1:B2,2,)

There is also a nice tutorial on youtube to show what i am talking about above visually

http://www.youtube.com/watch?v=ApS9oAYJMQo
0
 
orerockonAuthor Commented:
Fabulous THANK YOU!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now