vlookup crawling from different sheet in google sheet

[Asdf99]

Hi,

I'd like to ask a question, but I have asked this formula in few different forum before since no one has successfully given me the right comprehensive answer. Hereby, to abide by this forum's rule, I am listing down the links where I have asked the question. Here are where I have posted at :
https://www.excelguru.ca/forums/show...ed=1#post47783
https://forum.ozgrid.com/forum/index...s/#post1256490
https://www.mrexcel.com/board/thread...8/page-2#posts
https://www.myonlinetraininghub.com/...-crawls#p25604
https://techcommunity.microsoft.com/...563303#M152602

I posted there because this site were down for a day.


So my question is :


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 : https://prnt.sc/KO9_cq8rLBvF
Sheet 2 : https://prnt.sc/7gM6G4CsOxrm

This is for google excel sheet

Please check attached

[AliGW]

This works in MS365 - you could try it:

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

[Asdf99]

This works in MS365 - you could try it:

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

Hi thank you, I have tried this in google sheet, but the result are all blanks even though if it should have result.

Also, If I tried in excel, hence this is what had happened :
1. I opened your file, result was good
2. I clicked on the field (sheet 1 A2), (because I tried to copy - paste the formula to my google excel sheet.
3. then I let go of my cursor from the field
4. then the result became blank (A - in screenshot)

Please check this screenshot https://prnt.sc/SlWT4Um_TqWx

ask.png

for B - in screenshot, I was wondering, why the formula became like (IFERROR(_xlfn.TEXTJOIN(", ",1,_xlfn._xlws.FILTER() ; from (=IFERROR(TEXTJOIN(", ",1,FILTER)

[Asdf99]

In https://www.mrexcel.com/board/thread...2#post-5908551, by @fluff..

I have found the correct formula => =IFERROR(TEXTJOIN(", ",1,FILTER(Sheet2!A$2:A$4,ISNUMBER(FIND(" "&A2&" "," "&Sheet2!B$2:B$4&" ")))),"")
but issue with his formula is that, it needs to be accurate in terms of CAPS/not caps

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

[Pete_UK]

Try using SEARCH instead of FIND.

Hope this helps.

Pete

[Asdf99]

thank you very much, you have solved my problem !

[AliGW]

So on ExcelGuru, why did you say that it had been solved on Mr Excel?

For what it’s worth, this level of cross posting is something that really puts me off. I won’t be alone in thinking this way. I wouldn’t make a habit of it if I were you.

[Pete_UK]

thank you very much, you have solved my problem !

Glad to have helped. Thanks for the rep.

Pete