Lookup with partial match

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi there,

I need a lookup formula that compares a given value against a column in a table (with text of variable length) and returns a match if the lookup value begins with the whole of the matched string.

So for example, in this example, I need to match "DEFLPQS" with "DEF" in the lookup column because "DEFLPQS" starts with "DEF", and then return the corresponding value 20. If the value was "DE" it should not return a result because it has to start with the whole of the string in the lookup column. And the strings in the lookup column could be of any length.

ValueFormulaLookup ColumnReturn Value
DEFLPQSABCD10
DEF20
GHIJK30
WXYZ40

Also - if possible - I would really like for the formula to return a best match if more than one can be found ("best" meaning the longest string in the lookup column that satisfies the criteria)

So for example :

ValueFormulaLookup ColumnReturn Value
DEFLPQSABCD100
DEF200
DEFA300
DEFL400
DEFLP500

Here, the lookup value "DEFLPQS" matches against three different rows in the lookup column ("DEF", "DEFL" and "DEFLP") but I want to return the associated value for "DEFLP" (500) because that is the longest matching string in the lookup column (5 characters as opposed to 3 or 4) that meets the criteria.

I am used to using INDEX/MATCH for this kind of thing but only if the wildcard is appended to the value being looked up, not to all the values in the lookup column? And/or a LEFT() function.

Any suggestions?

Thanks!

AOB
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
If the lookup column in the lookup table is sorted, then you're just looking for the last match, so a LOOKUP formula would work? Or sort it descending, then use a normal VLOOKUP or INDEX MATCH with a wildcard and exact match.
 
Upvote 0
It's not sorted - or rather, I need the ability to be able to sort the lookup table but not necessarily on the lookup column in question, such that the formulas won't then recalculate and return different results.

I need a formula to return the value regardless of how the lookup table is sorted, if you get me?
 
Upvote 0
What version of Excel? (it would be useful to update your profile with that information)
 
Upvote 0
Office 365 (will update my profile now...)
 
Upvote 0
Then try this:

Excel Formula:
=IFERROR(INDEX(SORT(FILTER(C2:D6,IFERROR(FIND(C2:C6,A2),0)=1),1,-1),1,2),"No match")
 
  • Like
Reactions: AOB
Upvote 0
Solution
Okay this looks pretty awesome, I just need to fiddle around with it and get to grips with the concepts of [sort_index] and [sort_order] in the SORT function so I'm sure I'm adapting it correctly!

But looks promising!

Thank you so much as always @RoryA
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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