VLOOKUP partial text match

[stuartmacdonald]

I'm struggling to get a VLOOKUP formula with a wild card to work.
I have a list of 2,000 physical assets with a text name in Col A, e.g. Wholesale market, XYZ primary school, ZYX secondary school.
I want to categorize them with a code, so any asset name that contains the text 'market' is coded as 1, and any asset name with the text 'school' is coded a 2 etc
Have set up a second named table range called 'Category' where these text identifiers (type) are listed in the first column and codes (codes) are located in the 2nd column.

My formula looks at the asset name, seeks to make a partial match with my category types, and then return the code for that category.

=VLOOKUP(A2,"*"&Category[Type]&"*",2,FALSE)

Not working. What am I doing wrong?

 

[AlanY]

try

=VLOOKUP("*"&A2&"*",Category[Type],2,FALSE)

 

[stuartmacdonald]

try

=VLOOKUP("*"&A2&"*",Category[Type],2,FALSE)

Nope, did try that, but thought it was the bit that is partial that needed the wild card....

 

[AlanY]

care to post data that are not working here

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest August 2020 v 1.2.8 )

 

[stuartmacdonald]

You can see the Workbook here

 

[AlanY]

ok, try this

Wolverhampton Asset Register August 2020.xlsx
	A	B
1	assetname	Column1
2	Bilston Retail Market	7
3	Former Joinery Workshop, Wolverhampton Homes L&D Team	3
4	Market Car Park Cafe and Toilets	7
5	Wholesale Market	7
6	Refuse Tip at the The Droveway	#N/A
7	Former Wolverhampton Outdoor Market	7
8	The Bungalow - Wholesale Market	7
9	Woodthorne Primary School	1
10	Former All Saints Infants	#N/A
11	Ashmore Park Nursery School	1
12	Compton Park Recreation Centre	#N/A
13	Ormiston SWB Academy	#N/A
14	Bantock Primary School	1
15	Nishkam Primary School	1
Assets

Cell Formulas
Range	Formula
B2:B15	B2	=LOOKUP(2,1/ISNUMBER(SEARCH(Category!$A$2:$A$8,A2)),Category!$B$2:$B$8)

 

[stuartmacdonald]

ok, try this

Wolverhampton Asset Register August 2020.xlsx
	A	B
1	assetname	Column1
2	Bilston Retail Market	7
3	Former Joinery Workshop, Wolverhampton Homes L&D Team	3
4	Market Car Park Cafe and Toilets	7
5	Wholesale Market	7
6	Refuse Tip at the The Droveway	#N/A
7	Former Wolverhampton Outdoor Market	7
8	The Bungalow - Wholesale Market	7
9	Woodthorne Primary School	1
10	Former All Saints Infants	#N/A
11	Ashmore Park Nursery School	1
12	Compton Park Recreation Centre	#N/A
13	Ormiston SWB Academy	#N/A
14	Bantock Primary School	1
15	Nishkam Primary School	1
Assets

Cell Formulas
Range	Formula
B2:B15	B2	=LOOKUP(2,1/ISNUMBER(SEARCH(Category!$A$2:$A$8,A2)),Category!$B$2:$B$8)

Perfect, thank you so much.....

 

[AlanY]

you're welcome