Search multiple keywords in a string

[JustinCl]

Hi All

I am busy building a spreadsheet that must do the following.

	A	B	C	D	E
1	20131101	Payment received 1151 John	168.57		1151
2	20131201	Payment received 1153 DLAMINI	224.39		1152
3	20131101	Payment received 1121 DAY-GARDEN	365.57		1153
4	20131101	Payment received 1951 MPHELA	641.76		1154
5	20131101	Payment received 1751 DEBEER	208.12		1155
6	20131101	Payment received 1931 COLSEN	62.14		1156
					Mphela

<tbody>
</tbody>

Above is a basic example of the data I am using
What the spreadsheet must do is it must search cell B1 for the key word in in cell E1, if it does not find the keyword, it must search the same cell for the keyword in cell E2 and so on if it finds the key word it must return a consatination of cell A1 and the Keyword.

This is easy enough to do using the search function together with a couple of nested IF statements but where the problem comes in is that I am needing to run the search on about 12000 lines using over 10000 key words.

currently I have sheet with a formula in Column A containing 40 nested IFs searching for the first 40 key the same formula in Column B searching for the next 40 keywords and so on but to search 10 000 keywords(Client name or Client number) I am needing to apply this formula to 250 columns over 12000 line which makes the sheet very resource incentive.

How do I streamline this to make the spreadsheet more efficient


Thanks

<tbody>
</tbody>

 

[Domenic]

Try...

D1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ISNUMBER(MATCH(TRUE,ISNUMBER(FIND(" "&$E$1:$E$6&" "," "&B1&" ")),0)),A1&" - "&INDEX($E$1:$E$6,MATCH(TRUE,ISNUMBER(FIND(" "&$E$1:$E$6&" "," "&B1&" ")),0)),"")

If you're using Excel 2007 or later version, try...

D1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IFERROR(A1&" - "&INDEX($E$1:$E$6,MATCH(TRUE,ISNUMBER(FIND(" "&$E$1:$E$6&" "," "&B1&" ")),0)),"")

 

[ChrisM]

This would be work a lot easier if you first isolate the column B codes in their own column first, then you could simply use a vlookup to get your answer. From your sample, it looks like the code is always a four digit code after "payment received", so you could add a new column F with this formula:

=Mid(B1,18,4)

That will return just "1151"

Now in column G use this formula:

=If(IsError(Vlookup(F1,E1:E10000,1,0),"",A1 & Vlookup(F1,E1:E10000,1,0))