Vlook Up Crawls

[asdf_99]

Hi

I'd like to crawl a keyword from sheet 2 colomn A:B (consist of multiple text and sentences) into sheet 1 colomn B (noting from sheet 2 colomn A). Please tell me the formula.

Thank You

sheet 1:

sheet 2:

 

[asdf_99]

Because i usually post in excelforum.com and they can reply fast and very well..., but right now they are down... so I am really in need of a such a great help

 

[Peter_SSs]

I thought one forum may have different people in the community

They do, but if you read the rule and extra link that was mentioned, you would know why we want to know about cross-posts.

#13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

when do I can receive an answer ?

In a free public forum like this there is no particular answer to that. All helpers are volunteers and are free to answer or leave any question they choose.

I even have tried this on ms excel offline does not works

It does work - as you can see my actual mini-sheet in post #2 with the formulas producing the results you said that you wanted.
Were you using Excel 2021 which your profile says or some earlier version of Excel?
In what way did it not work in Excel? (no results, wrong results, error etc)

Because i usually post in excelforum.com and they can reply fast and very well...

Well, you did get a working Excel answer here in 7 minutes!

 

[asdf_99]

I am asking this same question few different forums, here where I am posting at :
https://www.excelguru.ca/forums/show...Crawls&p=47765
https://www.mrexcel.com/board/thread...2#post-5908402
https://techcommunity.microsoft.com/...ls/m-p/3562564
https://www.myonlinetraininghub.com/...-crawls#p25593

 

[asdf_99]

Well, you did get a working Excel answer here in 7 minutes!

Hi thank you for trying the answer, but hence the formula you have had given it stated 'not a valid function' Screenshot

 

[Peter_SSs]

Perhaps in your regional version of Excel the function argument separator is a semicolon ( rather than a comma (,)?
Does something in the formula get highlighted when you click OK to that message?

 

[Fluff]

With sheets try

=IFERROR(TEXTJOIN(", ",1,FILTER(Sheet2!A$2:A$4,ISNUMBER(FIND(" "&A2&" "," "&Sheet2!B$2:B$4&" ")))),"")

 

[asdf_99]

With sheets try

=IFERROR(TEXTJOIN(", ",1,FILTER(Sheet2!A$2:A$4,ISNUMBER(FIND(" "&A2&" "," "&Sheet2!B$2:B$4&" ")))),"")

Thank you very much, You did very well but still not perfect..

The issue with Your formula is that, it needs to be accurate in terms of CAPS/not caps

so if the words is adam in the first sheet but then if in the second sheet is Adam, it would not crawl..
unless if in the second sheet is adam also..

 

[Fluff]

In that case change FIND to SEARCH.

 

[asdf_99]

thank you very much, you have solved my problem !

 

[Fluff]

Glad we could help & thanks for the feedback.