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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
try

=VLOOKUP("*"&A2&"*",Category[Type],2,FALSE)
 
Upvote 0
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 )
 
Upvote 0
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)
 
Upvote 0
Solution
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.....
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top