Vlookup Formula Required

[gandhikr]

Hello,

I have below data in Excel:

Campaign name Ad group Search term Remark for list item Keyword Impressions Clicks Average CPC Spend LIST
Bitdefender bitdefender others avangate bitdefender avangate bitdefender 4 0 0 0 remove
Bitdefender Bitdefender Support bd support bitdefender bd support bitdefender 1 0 0 0 delete
Bitdefender bitdefender Exact bit defender bitdefender 18 0 0 0 install
Bitdefender bitdefender Exact bitdefender bitdefender 59 1 396.28 396.28 setup
Bitdefender bitdefender 2014 Exact bitdefender 2014 bitdefender 2014 9 0 0 0 set up
Bitdefender bitdefender others avangate bitdefender avangate bitdefender 4 0 0 0 buy
Bitdefender Bitdefender Support bd support bitdefender bd support bitdefender 1 0 0 0 support
Bitdefender bitdefender Exact bit defender bitdefender 18 0 0 0 price
Bitdefender bitdefender Exact bitdefender bitdefender 59 1 1.69 396.28 license
Bitdefender bitdefender 2014 Exact bitdefender 2014 bitdefender 2014 9 0 0 0 licence
Bitdefender bitdefender review bitdefender 2014 reviews bitdefender 2014 reviews 1 0 0 0 review


I want a Excel formula which should search data from LIST column into Search Term Column. If it finds any of the word in the search term colmn, it should write that word in adjacent cell (Remark for the List item - column).

The output should be as under:
AFTER
Campaign name Ad group Search term Remark for list item Keyword Impressions Clicks Average CPC Spend LIST
Bitdefender bitdefender others avangate bitdefender avangate bitdefender 4 0 0 0 remove
Bitdefender Bitdefender Support bd support bitdefender support bd support bitdefender 1 0 0 0 delete
Bitdefender bitdefender Exact bit defender bitdefender 18 0 0 0 install
Bitdefender bitdefender Exact bitdefender bitdefender 59 1 396.28 396.28 setup
Bitdefender bitdefender 2014 Exact bitdefender 2014 bitdefender 2014 9 0 0 0 set up
Bitdefender bitdefender others avangate bitdefender avangate bitdefender 4 0 0 0 buy
Bitdefender Bitdefender Support bd support bitdefender support bd support bitdefender 1 0 0 0 purchase
Bitdefender bitdefender Exact bit defender bitdefender 18 0 0 0 price
Bitdefender bitdefender Exact bitdefender bitdefender 59 1 1.69 396.28 license
Bitdefender bitdefender 2014 Exact bitdefender 2014 bitdefender 2014 9 0 0 0 licence
Bitdefender bitdefender review bitdefender 2014 reviews review bitdefender 2014 reviews 1 0 0 0 review


Sample file is attached Sample Worksheet1.xlsx

I need a vlookup / other formula to do it..

Thanks in advance for help.

[RudiS]

Hi,

Here is a formula you can use:
Note: Put the formula into cell D4 and press CTRL+SHIFT+Enter to confirm and then autofill it down.

=IFERROR(INDEX($J$4:$J$14,MATCH(TRUE,ISNUMBER(FIND($J$4:$J$14,C4)),0)),"")

Please note that the formula is designed by HansV from Eileen's Lounge (http://www.eileenslounge.com/portal.php)

[gandhikr]

Hello Rudi,

Thanks for the help.

I put the formula in the cell below Remark for list item, but the cell remains blank

File is again attached for your reference Sample Worksheet1.xlsx

Could you attach a working file and let me have a look, plesae..

Thanks again..

[RudiS]

Hi,

I don't think you pressed CTRL+SHIFT+Enter to confirm the formula. This converts it into an array formula which calculates differently from notmal formulas. Once you have entered it, then autofill down.

The file attached has a working example.