Bulk Search Keywords and Return to Values

[Koala123]

Hi pros, Could I please get your 2 cents on this?

I have a set of data which contains some keywords that can be used as parameters to do some search, my idea is to use my keywords list to check if any of them are in the data list, is yes then return to a value.

To be precise, if A2:A20 is the original data, I want to search if any keyword in reference column can be found in A2:A20, if yes, then return values from reference results to cells next to the original data table. If A2 can't find any thing from reference list, then move to A3........

I know "isnumber + search" can do keywords lookup but seems it doesn't work on bulk search, so could anyone shed a light on this please?

Thanks heaps!

[TABLE="class: grid, width: 859"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Description[/TD]
[TD]Amount[/TD]
[TD]Contract number[/TD]
[TD][/TD]
[TD][/TD]
[TD]Reference[/TD]
[TD]Contract number[/TD]
[/TR]
[TR]
[TD]Kirby Soza repayment 100600[/TD]
[TD="align: right"]484[/TD]
[TD]　???????[/TD]
[TD][/TD]
[TD][/TD]
[TD]Kirby Soza[/TD]
[TD="align: right"]111111[/TD]
[/TR]
[TR]
[TD]Payment CBA Tisha Hosch[/TD]
[TD="align: right"]665[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Tisha Hosch[/TD]
[TD="align: right"]222222[/TD]
[/TR]
[TR]
[TD]Glayds Friedrichs 5603334[/TD]
[TD="align: right"]246[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Glayds Friedrichs[/TD]
[TD="align: right"]333333[/TD]
[/TR]
[TR]
[TD]Merrilee Piano repayment 100600[/TD]
[TD="align: right"]492[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Merrilee Piano[/TD]
[TD="align: right"]444444[/TD]
[/TR]
[TR]
[TD]Payment CBA Ramiro Steele[/TD]
[TD="align: right"]252[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ramiro Steele[/TD]
[TD="align: right"]555555[/TD]
[/TR]
[TR]
[TD]12376734 Bernice Petrie ANZ bank[/TD]
[TD="align: right"]360[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bernice Petrie[/TD]
[TD="align: right"]666666[/TD]
[/TR]
[TR]
[TD]Roger Semmes 5603334[/TD]
[TD="align: right"]231[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Roger Semmes[/TD]
[TD="align: right"]777777[/TD]
[/TR]
[TR]
[TD]Dede Klemm repayment 100600[/TD]
[TD="align: right"]457[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dede Klemm[/TD]
[TD="align: right"]888888[/TD]
[/TR]
[TR]
[TD]12376734 Rosalia Damon ANZ bank[/TD]
[TD="align: right"]708[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Rosalia Damon[/TD]
[TD="align: right"]999999[/TD]
[/TR]
[TR]
[TD]Payment CBA Marilou Hickox[/TD]
[TD="align: right"]428[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Marilou Hickox[/TD]
[TD="align: right"]1111110[/TD]
[/TR]
[TR]
[TD]Louisa Arana 5603334[/TD]
[TD="align: right"]350[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Louisa Arana[/TD]
[TD="align: right"]1222221[/TD]
[/TR]
[TR]
[TD]Shelia Tollett repayment 100600[/TD]
[TD="align: right"]337[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shelia Tollett[/TD]
[TD="align: right"]1333332[/TD]
[/TR]
[TR]
[TD]12376734 Jovita Rolfe ANZ bank[/TD]
[TD="align: right"]707[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jovita Rolfe[/TD]
[TD="align: right"]1444443[/TD]
[/TR]
[TR]
[TD]Payment CBA Karissa Galeano[/TD]
[TD="align: right"]149[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Karissa Galeano[/TD]
[TD="align: right"]1555554[/TD]
[/TR]
[TR]
[TD]Payment CBA Deeanna Copp[/TD]
[TD="align: right"]203[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Deeanna Copp[/TD]
[TD="align: right"]1666665[/TD]
[/TR]
[TR]
[TD]Mariah Hickson 5603334[/TD]
[TD="align: right"]673[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mariah Hickson[/TD]
[TD="align: right"]1777776[/TD]
[/TR]
[TR]
[TD]Janette Burrow repayment 100600[/TD]
[TD="align: right"]408[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Janette Burrow[/TD]
[TD="align: right"]1888887[/TD]
[/TR]
[TR]
[TD]Beverley Vangorder 5603334[/TD]
[TD="align: right"]630[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beverley Vangorder[/TD]
[TD="align: right"]1999998[/TD]
[/TR]
[TR]
[TD]Payment CBA Cassondra Tabb[/TD]
[TD="align: right"]299[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cassondra Tabb[/TD]
[TD="align: right"]2111109[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Care to specify the results for C2, C3, and C4 for example?

 

[Koala123]

Hi Aladin, answer to C2 is 111111, as it's derived from "Kirby Soza" and corresponding contract number, same, C3 should be 222222 and C4 is 333333, I was trying to create some sort of a lookup formula to get the contract number

 

[Aladin Akyurek]

Hi Aladin, answer to C2 is 111111, as it's derived from "Kirby Soza" and corresponding contract number, same, C3 should be 222222 and C4 is 333333, I was trying to create some sort of a lookup formula to get the contract number

In C2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$F$2:$F$20&" "," "&$A2&" "),$G$2:$G$20)

 

[Koala123]

This is ridiculously awesome! Thank you very much Aldadin, I have tried it with my sample and it worked perfect!

 

[Aladin Akyurek]

This is ridiculously awesome! Thank you very much Aldadin, I have tried it with my sample and it worked perfect!

Glad to help.