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!

Description	Amount	Contract number			Reference	Contract number
Kirby Soza repayment 100600	484	???????			Kirby Soza	111111
Payment CBA Tisha Hosch	665				Tisha Hosch	222222
Glayds Friedrichs 5603334	246				Glayds Friedrichs	333333
Merrilee Piano repayment 100600	492				Merrilee Piano	444444
Payment CBA Ramiro Steele	252				Ramiro Steele	555555
12376734 Bernice Petrie ANZ bank	360				Bernice Petrie	666666
Roger Semmes 5603334	231				Roger Semmes	777777
Dede Klemm repayment 100600	457				Dede Klemm	888888
12376734 Rosalia Damon ANZ bank	708				Rosalia Damon	999999
Payment CBA Marilou Hickox	428				Marilou Hickox	1111110
Louisa Arana 5603334	350				Louisa Arana	1222221
Shelia Tollett repayment 100600	337				Shelia Tollett	1333332
12376734 Jovita Rolfe ANZ bank	707				Jovita Rolfe	1444443
Payment CBA Karissa Galeano	149				Karissa Galeano	1555554
Payment CBA Deeanna Copp	203				Deeanna Copp	1666665
Mariah Hickson 5603334	673				Mariah Hickson	1777776
Janette Burrow repayment 100600	408				Janette Burrow	1888887
Beverley Vangorder 5603334	630				Beverley Vangorder	1999998
Payment CBA Cassondra Tabb	299				Cassondra Tabb	2111109

<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[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.