Formula to find specific text in a cell from a reference table?

Can someone help me with a formula to search the contents of each cell for specific text from a reference table?

Eg.
cell A1: EFTPOS COLES WATERLOO NSW AU
cell A2: EFTPOS DANKS ST PHARMACY 0001 WATERLOO
cell A3: EFTPOS COLES WATERLOO NSW AU
cell A4: EFTPOS THE REJECT SHOP EAST PAGEWOOD NS
cell A5: EFTPOS HANDYWAY AVENUE ON 0001 SYDNEY

Reference table
COLES
REJECT
AVENUE


Essentially I want to put a formula in each cell that searches A1, A2 etc for any of the words in the reference table below & the result is to put that word in cell B1 (COLES), B2 (n/a), B3 (COLES), B4 (REJECT), B5 (AVENUE) etc

Can someone suggest a simple way of doing this?

Thanks

Hi i think this may help you. Pls try out the below solution & also the attached reference file
=MID(A5,FIND(C5,A5,1),LEN(C5))

Originally Posted by TAMILARASAN

Hi i think this may help you. Pls try out the below solution & also the attached reference file
=MID(A5,FIND(C5,A5,1),LEN(C5))

Thanks - this was not really what I was looking for. I want to have the options "Coles, Reject" etc in a dropdown list that I can reference to so that the formula in each line finds the content. (not sure if that makes sense). I think the previous solution works for me.

Originally Posted by Fotis1991

Hi and welcome to the forum.

Try this formula in B1 and copy down. Also change the semi-colons to gomma, if you have to do it.

=IF(ISERROR(FIND("COLES";A1;1));IF(ISERROR(FIND("REJECT";A1;1));IF(ISERROR(FIND("AVENUE";A1;1));"";"AVENUE");"REJECT");"COLES")

Hi Fotis1991 - this is great.

One other question - can I point the fields I want to lookup to a list or range, rather than using the lookup names (avenue, reject, coles etc) in the formula.
Reason I ask is that my example had only 3 items, but in reality I may have 30 items in my reference field I want looked up & if I used 30 items in the formula it will get so big & unmanageable.

thanks

Please mark this as solved if you have received a satisfactory answer.

Originally Posted by Fotis1991

You mean, something like this??

Please Login or Register  to view this content.

Thanks Fortis. It sort of helps - perhaps I am not explaining properly.
What you have proposed works, but the number of arguments I can use in the formula seem to be limited to 6 or 7.

- From the attachement, there is a large list of data in Column A.
- What I would like to do, it put a formula against each row of data in Col D.
- This forumula should identify if any of the words in I1:I16 are found in each row of Col A & if it finds that word in the text string, I would like to see that word as the result in Col D.

So what you proposed works, but I need to use a much longer list of keys words & dont want to be limited to 6.
Hopefully this clarifies.

Fortis & Haseeb - thanks for the help. Both solutions work

Haseeb, what is the reference in the Lookup formula below pointing at or what does it mean: 9E300 ?

Originally Posted by Haseeb A

Try...

=IFERROR(LOOKUP(9E300,SEARCH(I$1:I$16,A1),I$1:I$16),"")

copy down

@larrens:

as per my understanding - 9E300 indicates a ridiculously large value. it could have been 2^15 (as in @Fotis's formula) or 99^99, all the same. the idea is to establish a 'most-out-there' value.