How to lookup only partially-matched value?? – Crazy lookup!!!

This is a real workplace problem.

Look at the two tables below, the task was to map the Shop ID from Table 1 (left) to Table 2 (right).  In both tables there is a common key “Shop Name”.  Sound easy? Pls look at the tables first.


The so-called common key appears to be totally different in the two tables.  How could it be possible to do the mapping by formula when the values in the common key are so special??

If not by formula, was I supposed to do that manually by eye-balling?  (The list is much long than what is shown above… ) That stopped me for a second look at the tables again.  Thanks GOD, there is one “commonality” in the “Shop Name”.  Do you see?


In Table 2, when all spaces in shop names are taken out, we see the “Shop Name” somewhere in the middle of the “Shop Name” in Table 1.  I see light in the darkness because the task can be done by formula and be completed in just a minute…  How? Let’s do it step by step.

Step 1 – Remove “Space” in the lookup values by using function SUBSTITUTE


The Sytax

=Substitute(Text,Old Text,New Text,[instance number])

In the example, it simply means: Look at the text in “D3”, substitute space (” “) with nothing (“”).  As [instance number] is omitted, Excel will substitute all spaces found.

Result: “Danang1

Step 2 – Add “*” in front of and after the above result by using “&” for subsequent lookup


The “&” operator serves the same function as CONCATENATE.

Now the result is: “*Danang1*

Note: * is a wildcard meaning text string of any sequences.

*Danang1*” now serves the purpose of looking up from Table 1.

Step 3 – Identify the relative position of the above result under Shop Name in Table 1


The Syntax


In this example, the lookup_value is the result in Step 2 that is “*Danang1*“.  Literally the formula means: Look up any text that contains “Danang1” in between, from the range of B3:B100 where holds the Shop Name in Table 1.  Once a matched value is found, returns the relative positive in the range.  Exact match pls.

Result: “4″ 

Step 4 – Get the corresponding Shop Name by using INDEX


The Syntax


It simple means in our example that under “Shop No” (A3:A100) in Table 1, return the 4th value.

This is the final formula:

=INDEX($A$3:$A$100,MATCH(“*”&SUBSTITUTE(D3,” “,””)&”*”,$B$3:$B$100,0))

Result: “9999/1004


Step 5 – Get the job done by filling down




Final Step – Relax and enjoy a cup of your favorite drink

If you were me, would you submit the result to your boss immediately?? 😛


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.