In this article, I will show you how to use Excel to look up partial text matches. For this, we can use **VLOOKUP** or **INDEX MATCH** functions combo.

In the following image, you are seeing some company names, their origin country, and market category. We want to return the full company name using a partial match of the name.

Say, if we look up with **Diamond**, we want that full company name **“Aber Diamond Ltd.”** will return. Or if we search with **Abbey, **we want **“Abbey National plc”** will be returned.

## Excel lookup partial text match (using VLOOKUP)

In cell G3, we use this formula: **=VLOOKUP(“*”&F3&”*”,company,1,0)**

And then copy-paste the formula for other cells in the column. And this is what we get.

**How does this formula work?**

This is a very simple formula. Only the complex part of this formula is this part: **“*”&F3&”*”**

If we use the value of cell **F3** as the replacement, it will look like **“*Diamond*”**.

This character (*****) is actually Excel’s wildcard character and it is replaced by any characters.

So, this text **“*Diamond*” **means: it is any text that has **Diamond**, also having any characters before and after **Diamond**.

We are using **“*Diamond*” **as **VLOOKUP** function’s **lookup_value**. So, VLOOKUP will search for any text in the **lookup_array** that contains **Diamond** text.

I hope this explanation clears the idea of how this formula actually works.

## Excel lookup partial text match (using INDEX Match combo)

This time, we shall use this formula in cell **G3: =INDEX($B$3:$B$21,MATCH(“*”&F3&”*”,$B$3:$B$21,0))**

And we get the same results when copy and paste the formula for other cells in the column.

Check out the following page.

## Download Working File

These are my two ways of using Excel to lookup a partial text match. If you know of any other technique to do this, let me know in the comment box.

## Further Readings

- How to Use SORT Function in Excel (4 Examples)
- How to use XLOOKUP function in Excel (7 Examples)
- How to use MATCH function in Excel (3 Examples)
- How to Use HLOOKUP Function in Excel (8 Suitable Approaches)
- How to Use FILTER Function in Excel (9 Easy Examples)
- VLOOKUP and HLOOKUP combined Excel formula (with example)
- How to Find Duplicate Values in Excel using VLOOKUP

Thanks Sir,

It’s Very Good Idea.

I,HAVE LEARNED SOMETHING NEW

Thanks for your feedback.

Fantastic, very simple, congratulations

Thank you for your appreciation, FERREIRA!

This is great! But how can I get multiple result on 1 lookup value?

As always great material! Clear, concise, easy to understand (Even Excel Novice Can Follow KA’s Simple Lesson Examples, to grasp concepts) Need I say more!

Thank you for your appreciation, JUSTIN!

Hi Kaiser

A question, what if I have 3 Diamond in same column, how would you add to get the other 2 company matches using a formula?

Regards

Tony

Hi TONY O’BRIEN,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To get multiple companies, you can use the formula below in

Cell G13:`=INDEX($B$3:$B$21,SMALL(IF(ISNUMBER((SEARCH($F$13,$B$3:$B$21))),MATCH(ROW($B$3:$B$21),ROW($B$3:$B$21)), ""),ROWS($A$1:A1)),COLUMNS($A$1:A1))`

Remember, it is an array formula. You can follow the steps below to get the results.

1. Firstly, type

DiamondinCell F13.2. Secondly, type the above formula in

Cell G13.3. Press

Ctrl+Shift+Entertogether.4. After that, drag the

Fill Handledown to get all 3 values.For more information, you can check the article below.

https://www.exceldemy.com/index-match-multiple-criteria-partial-text/

I hope this will help you to solve your problem.

Thanks!

Wonderful formula Kawser

Hi, VIJAY!

Thank you for your feedback!

i have the addresses of multiple clients. I want to match these addresses how many clients are with the same address. But the address is written in different ways. How to match these addresses by matching some letters partially. example is as under

1)Bhandari JS, HOUSE NO. 112-116-1205/1, R STREET, JIMTARA GROUND, SOANDSO VILLAGE 100001

2) JS Bhandari, H no.112/116-1205/1, street R, ZIMTARA GROUNDS, SO&SO VILL, PIN

HOW TO MATCH THESE TWO CELLS. WHAT IS THE FORMULA TO BE USED

Hi A V S S PRASAD,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To match the addresses, you need to use the

INDEX-MATCHfunctions. You will find similar formulas in the articles below.https://www.exceldemy.com/index-match-multiple-criteria-partial-text/

https://www.exceldemy.com/index-match-partial-match/

I hope this will help you to solve your problem.

Thanks!