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

[tonywatsonhelp]

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?

any ideas please?

Thanks

Tony

 

[XOR LX]

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

 

[tonywatsonhelp]

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
Thanks for all your help

Tony

 

[XOR LX]

Great! Glad to hear it!

Cheers