need to lookup txt in table within a cell reference

[kris01]

Hi,

Need to look up a certain values in table

A
1 apple
2 Pear
3 Grapes
4 Strawberries

and search for this words within a certain cell reference


H
2 I love apples.

If the word appears then return value "apple".

any ideas on how I can do this

[Pete_UK]

To see this in context, it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[kris01]

ok I've attached a file.

My formula starts in BE2

I want to look up the values in my table. BJ3:BJ25

and search these values in my cell H2. If the table value appears within Cell H2 then return that value.

[Pete_UK]

A picture of a worksheet is no good for working on - please attach a sample Excel workbook, as requested.

Pete

[kris01]

as requested....

Formula needs to be in F2

[johnmpl]

Hi, kris01!

You could try this formula:
=LOOKUP(8^5,SEARCH(I$3:I$7,D2),I$3:I$7)

Blessings!

[kris01]

Brilliant. Worked perfectly.

How do I remove the #N/a ?

[kris01]

What does the 8^5 function mean?

[AliGW]

Try this:

=IFNA(LOOKUP(8^5,SEARCH(I$3:I$7,D2),I$3:I$7),"")

The 8^5 (8 to the power of 5, in other words 8 x 8 x 8 x 8 x 8) is just a very large number. You could use 99999999999999 if you wanted to.

[kris01]

What does it tell excel to do in this instance?

[AliGW]

If you use Evaluate Formula on the Formulas ribbon you will see what the formula is doing. Essentially, the search returns a number in an array, so the LOOKUP needs to match a number with whatever is found there.

In this case, this will work just as well:

=LOOKUP(1,SEARCH(I$3:I$7,D2),I$3:I$7)

[kris01]

perfect. Thank you