Vlookup formula - partial text search

[fredericomarquez]

Hi, 

I am trying to use the Vlookup formula to search by Keywords in a cell and bring the value for category. 

I have the following example, on the first column I have the debits (long text with a lot of information). I want to search by the Keyword and bring the category to the second column.

Someone can help?

DEBIT	CATEGORY			KEY WORD	Category
PURCHASE AUTHORIZED ON 08/25 CHEVROLET KANSAS CITY KS J651695106198 CARD 1234				CHEVROLET	CAR
PURCHASE AUTHORIZED ON 08/23 ACE HARDWARE NEW YORK NY J51981981981981 CARD 1234				ACE HARDWARE	UTILITIES
PURCHASE AUTHORIZED ON 08/20 CHICK-FIL-A #1234 BOSTON MA Q1651651651981 CARD 1234				MCDONALD'S	FAST FOOD
PURCHASE AUTHORIZED ON 08/20 MCDONALD'S F1244 NEW YORK NY Q7161968168168 CARD 1234				CHICK-FIL-A	FAST FOOD
PURCHASE AUTHORIZED ON 08/20 WALMART F1244 NEW YORK NY Q7161968168168 CARD 1234				WALMART	GROCERY
PURCHASE AUTHORIZED ON 08/20 WALMART F1244 NEW YORK NY Q7161968168168 CARD 1234				WHOLE FOODS	GROCERY
PURCHASE AUTHORIZED ON 08/20 WHOLE FOODS F1244 NEW YORK NY Q7161968168168 CARD 1234

[#2]

@fredericomarquez

Hi,

VLOOKUP doesn't help in this situation, you need to a special formula like this:

=INDEX($F$2:$F$7,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$7,A2)),),0))

Regards

[#3]

@fredericomarquez 

The formula in B2 of the attached file, copied down rows, is: 

=LOOKUP(2,
1/(ISNUMBER(SEARCH(E$2:E$7,A2))),
F$2:F$7)

[#4]

Hello @fredericomarquez, 

I have a similar question.  I wondered if you have find a solutions to your initial problem.

Regards,

Johan