orerockon

asked on

# 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...

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Fabulous THANK YOU!

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