Bulk Search Keywords and Return to Values

Koala123

New Member
Joined
Apr 13, 2019
Messages
15
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!

DescriptionAmountContract numberReferenceContract number
Kirby Soza repayment 100600484 ???????Kirby Soza111111
Payment CBA Tisha Hosch665 Tisha Hosch222222
Glayds Friedrichs 5603334246 Glayds Friedrichs333333
Merrilee Piano repayment 100600492 Merrilee Piano444444
Payment CBA Ramiro Steele252 Ramiro Steele555555
12376734 Bernice Petrie ANZ bank360 Bernice Petrie666666
Roger Semmes 5603334231 Roger Semmes777777
Dede Klemm repayment 100600457 Dede Klemm888888
12376734 Rosalia Damon ANZ bank708 Rosalia Damon999999
Payment CBA Marilou Hickox428 Marilou Hickox1111110
Louisa Arana 5603334350 Louisa Arana1222221
Shelia Tollett repayment 100600337 Shelia Tollett1333332
12376734 Jovita Rolfe ANZ bank707 Jovita Rolfe1444443
Payment CBA Karissa Galeano149 Karissa Galeano1555554
Payment CBA Deeanna Copp203 Deeanna Copp1666665
Mariah Hickson 5603334673 Mariah Hickson1777776
Janette Burrow repayment 100600408 Janette Burrow1888887
Beverley Vangorder 5603334630 Beverley Vangorder1999998
Payment CBA Cassondra Tabb299 Cassondra Tabb2111109

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

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Koala123

New Member
Joined
Apr 13, 2019
Messages
15
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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

New Member
Joined
Apr 13, 2019
Messages
15
This is ridiculously awesome! Thank you very much Aldadin, I have tried it with my sample and it worked perfect!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,575
Messages
5,469,477
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top