# Formula How to find a word within a cell that matches any in a row,

#### tonywatsonhelp

##### Well-known Member
Hi Everyone,
just got an amazing formula for finding a set of words within a cell that goes like this:
=LOOKUP(2^15,SEARCH({"","Opening soon","Help Needed","Closed down"},A1),{"","urgent","help","Closed"})

This is exactly what I was looking for, however I'm now thinking I could imporove on this by looking up the data in a column

So instead of putting the words "Opening soon","Help Needed","Closed down" into the formula I put them in Column C1 to C3
I've tried to amend the above but can't get it to do anything so my question would be how can I get the above formula to look at cells C1 to C3 instead of having the data in the formula?

Oh, and instead of giving "urgent","help","Closed" I want it to give the word in column d so D1 to D3 so C1 give D1 C2 gives D2 etc?

Thanks

Tony

Last edited:

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi again!

Good idea to put the values in actual cells.

It would be easiest though if you actually list them in reverse order of their priority, so that Opening Soon is in C1, Help Needed in C2 and Closed down in C3, with their corresponding values in D1:D3.

That way your formula would be (assuming you're using Excel 2007 or later):

=IFERROR(LOOKUP(2^15,SEARCH(C1:C3,A1),D1:D3),"")

Regards

Hi again,
This is going to be so helpful not just today but everyday, so a massive thanks again,
I'm very impressed with this formula

Tony

Cheers

Replies
2
Views
248
Replies
2
Views
1K
Replies
5
Views
221
Replies
3
Views
267
Replies
0
Views
656

1,203,455
Messages
6,055,540
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back