VLOOKUP partial text match

stuartmacdonald

New Member
Joined
May 26, 2009
Messages
48
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 )
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
ok, try this

Wolverhampton Asset Register August 2020.xlsx
AB
1assetnameColumn1
2Bilston Retail Market7
3Former Joinery Workshop, Wolverhampton Homes L&D Team3
4Market Car Park Cafe and Toilets7
5Wholesale Market7
6Refuse Tip at the The Droveway#N/A
7Former Wolverhampton Outdoor Market7
8The Bungalow - Wholesale Market7
9Woodthorne Primary School1
10Former All Saints Infants#N/A
11Ashmore Park Nursery School1
12Compton Park Recreation Centre#N/A
13Ormiston SWB Academy#N/A
14Bantock Primary School1
15Nishkam Primary School1
Assets
Cell Formulas
RangeFormula
B2:B15B2=LOOKUP(2,1/ISNUMBER(SEARCH(Category!$A$2:$A$8,A2)),Category!$B$2:$B$8)
 
Solution

stuartmacdonald

New Member
Joined
May 26, 2009
Messages
48
ok, try this

Wolverhampton Asset Register August 2020.xlsx
AB
1assetnameColumn1
2Bilston Retail Market7
3Former Joinery Workshop, Wolverhampton Homes L&D Team3
4Market Car Park Cafe and Toilets7
5Wholesale Market7
6Refuse Tip at the The Droveway#N/A
7Former Wolverhampton Outdoor Market7
8The Bungalow - Wholesale Market7
9Woodthorne Primary School1
10Former All Saints Infants#N/A
11Ashmore Park Nursery School1
12Compton Park Recreation Centre#N/A
13Ormiston SWB Academy#N/A
14Bantock Primary School1
15Nishkam Primary School1
Assets
Cell Formulas
RangeFormula
B2:B15B2=LOOKUP(2,1/ISNUMBER(SEARCH(Category!$A$2:$A$8,A2)),Category!$B$2:$B$8)
Perfect, thank you so much.....
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,114,419
Messages
5,547,819
Members
410,813
Latest member
Vhinzvirgo
Top