Solved

# Excel find text within cell in a range of cells.

Posted on 2013-05-21
7,865 Views
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
Question by:orerockon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 39185184
You can use wildcard character * with VLOOKUP

e.g.

in B4:

=VLOOKUP("*"&A4&"*",A1:B2,2,0)
0

LVL 11

Expert Comment

ID: 39185196
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

0

Author Closing Comment

ID: 39185399
Fabulous THANK YOU!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.