Home

Vlookup formula - partial text search

%3CLINGO-SUB%20id%3D%22lingo-sub-845029%22%20slang%3D%22en-US%22%3EVlookup%20formula%20-%20partial%20text%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845029%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20Vlookup%20formula%20to%20search%20by%20Keywords%20in%20a%20cell%20and%20bring%20the%20value%20for%20category.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20example%2C%20on%20the%20first%20column%20I%20have%20the%20debits%20(long%20text%20with%20a%20lot%20of%20information).%20I%20want%20to%20search%20by%20the%20Keyword%20and%20bring%20the%20category%20to%20the%20second%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20can%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDEBIT%3C%2FTD%3E%3CTD%3ECATEGORY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EKEY%20WORD%3C%2FTD%3E%3CTD%3ECategory%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F25%20CHEVROLET%20KANSAS%20CITY%20KS%20J651695106198%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECHEVROLET%3C%2FTD%3E%3CTD%3ECAR%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F23%20ACE%20HARDWARE%20NEW%20YORK%20NY%20J51981981981981%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EACE%20HARDWARE%3C%2FTD%3E%3CTD%3EUTILITIES%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F20%20CHICK-FIL-A%20%231234%20BOSTON%20MA%20Q1651651651981%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMCDONALD'S%3C%2FTD%3E%3CTD%3EFAST%20FOOD%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F20%20MCDONALD'S%20F1244%20NEW%20YORK%20NY%20Q7161968168168%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECHICK-FIL-A%3C%2FTD%3E%3CTD%3EFAST%20FOOD%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F20%20WALMART%20F1244%20NEW%20YORK%20NY%20Q7161968168168%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EWALMART%3C%2FTD%3E%3CTD%3EGROCERY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F20%20WALMART%20F1244%20NEW%20YORK%20NY%20Q7161968168168%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EWHOLE%20FOODS%3C%2FTD%3E%3CTD%3EGROCERY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPURCHASE%20AUTHORIZED%20ON%2008%2F20%20WHOLE%20FOODS%20F1244%20NEW%20YORK%20NY%20Q7161968168168%20CARD%201234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-845029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-845880%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20formula%20-%20partial%20text%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406161%22%20target%3D%22_blank%22%3E%40fredericomarquez%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVLOOKUP%20doesn't%20help%20in%20this%20situation%2C%20you%20need%20to%20a%20special%20formula%20like%20this%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(%24F%242%3A%24F%247%2CMATCH(TRUE%2CINDEX(ISNUMBER(SEARCH(%24E%242%3A%24E%247%2CA2))%2C)%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846102%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20formula%20-%20partial%20text%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406161%22%20target%3D%22_blank%22%3E%40fredericomarquez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20B2%20of%20the%20attached%20file%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F(ISNUMBER(SEARCH(E%242%3AE%247%2CA2)))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EF%242%3AF%247)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
fredericomarquez
Occasional Visitor

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?

 

DEBITCATEGORY  KEY WORDCategory
PURCHASE AUTHORIZED ON 08/25 CHEVROLET KANSAS CITY KS J651695106198 CARD 1234   CHEVROLETCAR
PURCHASE AUTHORIZED ON 08/23 ACE HARDWARE NEW YORK NY J51981981981981 CARD 1234   ACE HARDWAREUTILITIES
PURCHASE AUTHORIZED ON 08/20 CHICK-FIL-A #1234 BOSTON MA Q1651651651981 CARD 1234   MCDONALD'SFAST FOOD
PURCHASE AUTHORIZED ON 08/20 MCDONALD'S F1244 NEW YORK NY Q7161968168168 CARD 1234   CHICK-FIL-AFAST FOOD
PURCHASE AUTHORIZED ON 08/20 WALMART F1244 NEW YORK NY Q7161968168168 CARD 1234   WALMARTGROCERY
PURCHASE AUTHORIZED ON 08/20 WALMART F1244 NEW YORK NY Q7161968168168 CARD 1234   WHOLE FOODSGROCERY
PURCHASE AUTHORIZED ON 08/20 WHOLE FOODS F1244 NEW YORK NY Q7161968168168 CARD 1234     
      
      
2 Replies

@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

@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)

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies