If Cell contains string, then vlookup result

[balki]

Hey folks,
I searched for an answer on this, but maybe didn't know how to word it properly.

In my case below, I'm trying to create a rule set (Green table) when if the value is contained in the "Description" column of the main table, then a lookup will auto-classify the row based on the type. I hope that makes sense. I've dummied up the data below.
Ex. the first data row contains "Canadian Tire", therefore the type is "HomeStuff". The second line contains "Park", and is therefore "Parking", etc. The last one has no match, so it returns "Unknown".
Help? I feel like this should be easier than I'm making it, but I can't figure this one out for some reason.
Thank you all!

 

[etaf]

i have not copied all your sample as its an image

But try

=INDEX($I$2:$I$4,MATCH("*"&H2&"*",$B$2:$B$4,0))
adjust the range to suite your data

but, is there a chance the keyword maybe within a word , but you dont want
as this is looking for
ANYTHING first , then PARK and then anything gain
so it maybe part of word and so we may need to include space

Book3
	A	B	C	D	E	F	G	H	I
1				result				keyword	type
2		fred john happry park 1		parking				park	parking
3		fred john happry stuff 2		food				stuff	food
4		fred john happry candian tyre 3		home				candian tyre	home
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX($I$2:$I$4,MATCH("*"&H2&"*",$B$2:$B$4,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[balki]

Thank you ETAF... I'm sorry for the format in my original post. I have a work computer that is totally locked down.
I've attached a dummied up file to Google Drive below, and inserted your formula. It's close, but is doing some strange things to the first line, and subsequent lines where there are no matches.

A littler fine tuning perhaps?
Google Shared File

I really appreciate your support, thank you again!
-Balki

 

[etaf]

sorry, made an error
try
=IF(INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0))=0,"unknown",INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0)))

why alcohol ? you have LCBO in table
BUT LCRO in text

LookupTest (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Description	DR	CR	Total	AutoType		Expect to see:			Key Word	Type
2	4/14/22	Point of Sale - Interac RETAIL PURCHASE CANADIAN TIRE S	3.96		- 3.96	Vehicle		Vehicle			LCBO	Alcohol
3	4/14/22	Point of Sale - Interac RETAIL PURCHASE MILLENNIUM PARK	12.00		- 12.00	Parking		Parking			Park	Parking
4	4/14/22	Point of Sale - Interac RETAIL PURCHASE STUFF'D	14.71		- 14.71	Food		Food			Stuff	Food
5	4/14/22	Point of Sale - Interac RETAIL PURCHASE TOY SHOP OF PET	85.83		- 85.83	unknown		Unknown			Canadian Tire	Vehicle
6	4/14/22	Point of Sale - Interac RETAIL PURCHASE TOY SHOP OF PET	71.17		- 71.17	unknown		Unknown			shop of
7	4/14/22	Point of Sale - Interac RETAIL PURCHASE SAM'S PLACE	17.87		- 17.87	unknown		Unknown
8	4/14/22	Amazon.ca*1O1 AMAZON.CA, ON	13.32		- 13.32	unknown		Unknown
9	4/14/22	LCRO/RAO Purchase	25.00		- 25.00	unknown		Alcohol
10	4/14/22	Amazon.ca*1T2 AMAZON.CA, ON	10.55		- 10.55	unknown		Unknown
11
Master

Cell Formulas
Range	Formula
E2:E10	E2	=[@CR]-[@DR]
F2:F10	F2	=IF(INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0))=0,"unknown",INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0)))
C5	C5	=(15.98+29.99+29.99)*1.13
C6	C6	=157-C5

 

[Peter_SSs]

A couple of questions:

1. Is it possible that more than one of the key words could appear in a cell? (eg "Point of Sale - Interac RETAIL PURCHASE PARK STUFF") If so, what should happen?
2. Are you sure that you mean "contains string" per your title, not "contains word"? For example "Point of Sale - Interac RETAIL PURCHASE Red Parker" would be classified as "Parking"?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[balki]

sorry, made an error
try
=IF(INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0))=0,"unknown",INDEX($L$2:$L$10,MATCH(1,COUNTIF(B2,"*"&$K$2:$K$10&"*"),0)))

why alcohol ? you have LCBO in table
BUT LCRO in text

Brilliant! Works like a charm. I am rather happy that it isn't as easy as a solution as I was worried it would be. I consider myself fairly competent in excel and this one definitely had me stumped. I'm going to spend some time interrogating this formula so I can pull it apart and understand exactly what it's doing now that I know it works. Thank you so much! Really, really appreciate the effort and support!
Thank you!

(Oh, and P.S. LCRO was just a typo when I was dummying up data - good catch! I must have been drinking the alcohol in question?! ha ha)

 

[etaf]

you are welcome
be aware though as mentioned

but, is there a chance the keyword maybe within a word , but you dont want
as this is looking for
ANYTHING first , then PARK and then anything gain
so it maybe part of word and so we may need to include space

and by Peter_SSs

A couple of questions:

1. Is it possible that more than one of the key words could appear in a cell? (eg "Point of Sale - Interac RETAIL PURCHASE PARK STUFF") If so, what should happen?
2. Are you sure that you mean "contains string" per your title, not "contains word"? For example "Point of Sale - Interac RETAIL PURCHASE Red Parker" would be classified as "Parking"?

 

[balki]

A couple of questions:

1. Is it possible that more than one of the key words could appear in a cell? (eg "Point of Sale - Interac RETAIL PURCHASE PARK STUFF") If so, what should happen?
2. Are you sure that you mean "contains string" per your title, not "contains word"? For example "Point of Sale - Interac RETAIL PURCHASE Red Parker" would be classified as "Parking"?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

View attachment 63102

Thank you Peter! I've updated my profile, thanks for the hint there!
To answer your questions:
1. If both words were in the cell, I would just ask the user to expand on the "key word" to be more specific. I'm not overly worried about the frequency of this happening, as the data shows that there are a ton of entries, but the majority are repeats, minus transaction codes that are often embedded in the middle of the overall string of text. If this auto-populates 75% of the records, and the user needs to manually enter the last 25%, that will be a total win.
2. I am hoping this will work with a string, as there 'may' be some entries where "park" is many different parking lots, so it could read "Balki's Parking Garage" or "Balki Park and Ride", or "Balki FindParkAndGo". If the rules don't tweeze it out properly, then key words may need to be adapted to be more specific as mentioned in question 1 above.

Like I say, I'm really just try to make this incrementally better than it was, which it now is, so I'm super happy. In a few months, I may come back with my hat in my hands looking for something to narrow things down more, but I'll cross that bridge then. Thanks again for the help and suggestions!

 

[Peter_SSs]

Thanks for the clarifications & for updating your profile.

Since you have MS 365 and your data appears to be in a formal Excel table, you might consider this adaptation. With it, if there are multiple keywords (eg row7), then the formula will list all the relevant types.

I would also make the lookup table into a formal Excel table (if it isn't already). That way, formulas will automatically expand/contract ranges if the lookup values get added to or deleted from.
I have named that table as tblLookup
Then use this modified formula using the table nomenclature.

balki LookupTest.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Description	DR	CR	AutoType			Key Word	Type
2	14/04/2022	Point of Sale - Interac RETAIL PURCHASE CANADIAN TIRE S	3.96		Vehicle			LCBO	Alcohol
3	14/04/2022	Point of Sale - Interac RETAIL PURCHASE MILLENNIUM PARK	12.00		Parking			Park	Parking
4	14/04/2022	Point of Sale - Interac RETAIL PURCHASE STUFF'D	14.71		Food			Stuff	Food
5	14/04/2022	Point of Sale - Interac RETAIL PURCHASE TOY SHOP OF PET	85.83		Unknown			Canadian Tire	Vehicle
6	14/04/2022	Amazon.ca*1O1 AMAZON.CA, ON	13.32		Unknown
7	14/04/2022	LCRO/RAO/LCBO Purchase - Big Park Stuff	25.00		Alcohol, Parking, Food
8	14/04/2022	Amazon.ca*1T2 AMAZON.CA, ON	10.55		Unknown
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=TEXTJOIN(", ",1,FILTER(tblLookup[Type],ISNUMBER(SEARCH(tblLookup[Key Word],[@Description])),"Unknown"))