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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies