Vlookup and return the entire row(s) based on multiple texts

[EthanK]

Hello guys! I've been unable to resolve this issue for about 12 hours now, so thought I would ask experts for some help.

I have a list of transactions with date, description and amount that I copied/pasted from my PNC bank and I would like to lookup the descriptions and return the rows containing specific keywords (I have a list of keywords).

I have attached an example spreadsheet, the copied/pasted list is in the Consolidated List 2017 sheet and the Taxable Expenses 2017 sheet should automatically extract and return rows containing keywords in the list (on the right in Consolidated List 2017 sheet)

Your help would be tremendously appreciated! Thanks so much

[EthanK]

P.S. I am thinking asterisks should be included in the formula since I am looking for rows with descriptions containing *one of the keywords* in the sentence?

[samba_ravi]

Try below array formula and copy across
A6=IFERROR(INDEX('Consolidated List 2017'!A:A,SMALL(IF(MMULT(IFERROR((SEARCH(TRANSPOSE('Consolidated List 2017'!$H$5:$H$19),'Consolidated List 2017'!$B$6:$B$9)>0)+0,0),(ROW('Consolidated List 2017'!$H$5:$H$19)>0)+0)>0,ROW('Consolidated List 2017'!$B$6:$B$9)),ROWS(A$6:A6))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[EthanK]

It works, thank you so much!!!!