Lookup Partial String Match In Excel

**demokedes** · 06-23-2022, 05:28 PM

I am using Vlookup to link pais of value, where I am trying to link values not by exact values, but based on partual value. Similar to what is described here: exceljet.net/formula/partial-match-with-vlookup, but in opposite way.
In this example "Aya" is linked with the value "Ayala", while in my case I would like to achieve that my vlookup will recognize "Ayala" and connect it with "Aya" in my reference table.
How to achieve that?
I though I should replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

, but it is not working

**ELeGault** · 06-23-2022, 06:54 PM

Change FALSE to TRUE

Please Login or Register  to view this content.

Make sure your data is sorted Ascending as the data will read top to bottom and find the first Likeness...

Now if you know you want to match say off the first 3 char and then anything after you could get the LEFT(Value,3) for the lookup value

**demokedes** · 06-24-2022, 03:08 AM

Hey ELeGault,
Thank you for proposed solution. At first, I thought it is working what I am expecting, but than I did some initial testing and I am afraid it is not. I have a problem, when the value is not exactly the same. In that case it link it with the closest value. In above example If I will enter "Ayola" it will link it with "Aya", since it is the closes value, while I would like to get N/A for a strings that are not included in the initial string. Is it possible to catch such a cases?
Thank you in advance,
Rok

**AliGW** · 06-24-2022, 03:13 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**AliGW** · 06-24-2022, 03:51 AM

Any chance you could upload a workbook, please?

**demokedes** · 06-24-2022, 03:59 AM

Example is attached. In the first table it is shown How current formula is working. In the second it is shown, how I am expecting it to be.

**JohnTopley** · 06-24-2022, 04:09 AM

Try

=IFERROR(INDEX(Table4[Value],MATCH(LEFT(D11,3),Table4[String],0)),"")

**Glenn Kennedy** · 06-24-2022, 04:12 AM

Try this:

=IFERROR(1/(1/SUM(IF(ISNUMBER(SEARCH(Table4[String],D11)),Table4[Value],""))),"")

it works fine in O365. In your version of Excel it may be an array formulla, and will need CTRL-SHIFT-Enter to work.

**demokedes** · 06-24-2022, 06:28 AM

Glenn Kennedy,
Thank you very much. It is working exactly in a way...I wanted it in my example. But in my real case....values I would like to link to, are not values, but strings. In that case, formula is not working. Most probably it has something to do with "ISNUMBER" check...but I am not able to change it in a right way. I am attaching another example with string. How to change additional formula in a way it will work also for this case?

**wk9128** · 06-24-2022, 07:00 AM

Cell E11 formula

Formula:

Please Login or Register  to view this content.

Cell F11 formula

Formula:

Please Login or Register  to view this content.

**demokedes** · 06-24-2022, 07:40 AM

Hm, I am not sure, this is working as I am expecting. As I can see it is checking only the first 3 characters. I would like to check dynamically for any part of the string.
In the initialy formula, prapred by Glenn it was working...the only problem there was, that it was linking it to the numberic value which is not the case in my scenario.

**Glenn Kennedy** · 06-25-2022, 04:56 AM

1. Make sure your sample sheets represent what you REALLY have (text for text, numbers for numbers).

2. Do NOT extend the lookup table(col I&J) to include blank cells. The table will extend as needed ITSELF.

3. Use this (ENTER to set):

=IFERROR(LOOKUP(1000,SEARCH(Table4[String],A11),Table4[Value]),"")

**demokedes** · 06-25-2022, 05:50 PM

Glenn Kennedy,
Thank you very much. Your solution is exactly what I was looking for. You're the best.

Lookup Partial String Match In Excel

LinkBack

Thread Tools

Rate This Thread

Display

Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Re: Lookup Partial String Match In Excel

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel Match + Capture partial text string

Lookup if Partial String match exists

Lookup if Partial String match exists

[SOLVED] Lookup partial values and if match return partial value from another cell

Partial lookup from text string, return value from lookup list

[SOLVED] Vlookup, Match (Search or Find) partial string within string in a Cell

Complex matching with indirect lookup based on partial string match

Bookmarks

Bookmarks

Posting Permissions