Keyword Lookup utilizing Keyword Lists searching Column of Text

[emartinx]

Looking for formula that I can search a column of Text (Column G) to search for keywords that are listed on another column tab, and return if the applicable keywords in ajacent cells in a column (F). Formula will be written in cells in Column F...example shown in first two.."KROGER". Keywords to search by is on other Sheet in Column A.

[pre]

A      B               F        G                                        H
BANK	DATE		NAME	 DESCRIPTION	                         Amount
BOA	8/27/2012	KROGER	 KROGER 08/27 #3079 PURCHASE SAGE T  LA	 -60.87
BOA	8/27/2012	KROGER	 KROGER 08/27 #113 PURCHASE SAGE T  LA	 -55.21
BOA	8/27/2012	??? 	 FRESCOS COCINA  SAGE T  LA  3033726	 -42.8

[/pre]

 

[Luke M]

Welcome to the forums!


I think this array formula will do what you are looking for:

=IF(AND(ISERROR(SEARCH(Sheet1!$A$2:$A10,G2))),"Not Found",INDEX(Sheet1!$A:$A,MIN(IF(ISNUMBER(SEARCH(Sheet1!$A$2:$A10,G2)),ROW(Sheet1!$A$2:$A10)))))


Remeber to confirm the formula using Ctrl+Shift+Enter, not just Enter.

 

[SirJB7]

@Luke M

Hi!

Just tried it and seems to retrieve only the first value in word list (Sheet2!A2:A10), but a 0 (zero) when the Gn cell doesn't contain a valid word.

Regards!


EDITED: my mistake typing firstly.