Finding right look up/search functionality

[Hedgingthehedge]

Im having difficulty finding the right solution to identifying if a word is located within a column of sentences.

I have two tabs "master" and "Paste".

On the "master" tab I have a list of words in Column A. The "paste" tab is where I will be pasting a different output file. The goal is to identify/ find the cell in question from column A on the master tab with in Column E on the "Paste" tab. Column E is made up of cells holding various descriptions/ strings of words. If cell A2 is within Column E of the paste tab it populates the word within the cell in question.

=IFERROR(VLOOKUP($A2,'Paste'!$E:$E,1,0),"")

This is the formula I am currently trying to work from. It is located on "master" tab in column B next to the List of words I have compiled. I need to tweak it somehow to do exactly the same thing but have it work even if the word i'm trying to find is within a string of words in Column E of "Paste" tab. It only works when the word is by itself in column E (rightfully so).

I've tried embedding isnumber(search) iferror(index) variations but I cant quite figure out the right solution.

Any ideas?

Thanks!

 

[Fluff]

How about
=IFERROR(VLOOKUP("*"&$A2&"*",'Paste'!$E:$E,1,0),"")

 

[Hedgingthehedge]

Thanks Fluff! Now in terms of visual purposes- say once it identifies what im looking for- is there any way to have it populate what cell on the other tab it is located within for easy investigation purposes. example: it can tell me it found it in "E98" so I could go directly there to investigate vs blindly scrolling through the column?

 

[Hedgingthehedge]

Also any frequency advice if it is populating multiple times in column E how I could have that populate as well? ex: found it 4 total times? vs populating the cell contents

 

[Fluff]

How about
=IFERROR(ADDRESS(MATCH("*"&$A2&"*",Paste!$E:$E,0),5),"")

 

[Hedgingthehedge]

That didn't seem to populate. Instead of identifying which cell it is coming from.. in a separate column any idea how I can use that original formula...

=IFERROR(VLOOKUP("*"&$A2&"*",'Paste'!$E:$E,1,0),"")

to do a count and tell me how many times the content in my "a2" cell populates throughout column E

 

[Fluff]

You asked for this

example: it can tell me it found it in "E98"

Which is what the formula in post#5 does.
If you want to know how many times it appears have a look at the countif function

 

[Hedgingthehedge]

Thanks fluff.

=COUNTIF('Paste'!E:E,IFERROR(VLOOKUP("*"&$A219&"*",'Paste'!$E:$E,1,0),""))

I noticed on the cells that don't appear, the number "1047874" populates... the count works well for the other instances. what would be your fix for making that number show 0 vs 1047874 when it doesnt appear?

 

[Fluff]

Vlookup will only return the first time the value is found, so you need to get rid of it & just do a simple countif.