Partial match index match with array values shorter than match value

isa2904

New Member
Joined
Feb 4, 2020
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Problem:
Trying to run an index match_partial match on a long column of values to return the desired result but have been getting an #NA. Current formula without changes: =INDEX(level,MATCH("*"&J3,title,0))

So I'm trying to match these values to return "Middle Management" for values that contain "Director"
title return column
Assistant Director
Associate Director
Account Director
Account Specialist
Facilitator
...etc.

Lookup Array
title level
Director Middle Management
Coordinator Entry Level
Manager Intermediate
...etc ....etc

It seems the formula with wildcard value/concatenate will only work when the values in the Lookup Table are shorter than the lookup value.
I can't remove the text in the beginning to search because the number of characters differ for each lookup value. (i.e. =INDEX(level,MATCH(RIGHT(J2,LEN(J2)-9),title,0))

Anybody has a better, more accurate solution? Thank you in advance!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel
It is not appreciated how you have the data.
You could copy the cells and paste here or use the XL2BB tool.

title return column
Your data is like this:

Book1
A
1Assistant Director
2Associate Director
3Account Director
4Account Specialist
5Facilitator
Sheet

Or like this:
Book1
AB
1AssistantDirector
2AssociateDirector
3AccountDirector
4AccountSpecialist
5Facilitator
Sheet


______________________________________________________________
title level
Like this:

Book1
A
1Director Middle Management
2Coordinator Entry Level
3Manager Intermediate
Sheet

Or like this:
Book1
AB
1DirectorMiddle Management
2CoordinatorEntry Level
3ManagerIntermediate
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,127,914
Messages
5,627,608
Members
416,256
Latest member
Tammy Beaudoin

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
Top