The sample dataset showcases the **Addresses of Employees **in a company.

To check whether a a partial match for a given state name is in the **Address **column, enter “New” in the Search State column

- Enter the formula in
**F5**.

`=IF(D4="*"&$H$5&"*","Desired State","It's another State")`

The** asterisk sign (*)** is used as a wildcard to denote that any number of characters can be returned. The ** value_if_true **argument will return the partial match and the

**argument will return “It’s another State”.**

*value_if_false*Although the cell contains “New”, the formula returned “It’s another State”. The **IF **function doesn’t work with wildcards.

## Method 1 – Combining the IF and the COUNTIF Functions to get a Partial Match in Excel

__Steps:__

- Enter the following formula in
**F5**.

`=IF(COUNTIF(D5,"*"&$H$5&"*"),"Desired State","It's another State")`

**D5 **is the selected **Address**, and **H5 **refers to the **Search State**.

**Formula Breakdown**

- In the
**COUNTIF(D5,”*”&$H$5&”*”)**function,**D5**→ is theargument.*range***“*”&$H$5&”*”**→ is theargument.*criteria***Output**→**1**.

- In the
**IF**function,**COUNTIF(D5,”*”&$H$5&”*”)**→ is theargument.*logical_test***“Desired State”**→ is theargument.*[value_if_true]***“It’s another State”**→ is theargument.*[value_if_false]***Output**→**Desired State**.

- Press
**ENTER**.

You will see the following output in **F5 **as there is a partial match in **D5**.

- Drag down the Fill Handle to the next cell to see the result.

The formula returned “It’s another state”, as there was no partial match.

- Drag down the Fill Handle to the next cell to see the result.

- Drag down the Fill Handle to see the result in the rest of the cells.

There are partial matches in **F5**, **F7**, **F8**, and **F10**.

## Method 2 – Combining the Excel ISNUMBER and SEARCH Functions to get a Partial Match

__Steps:__

- Enter the following formula in
**F5**.

`=IF(ISNUMBER(SEARCH("*"&$H$5&"*",D5)),"Desired State","It's another State")`

**Formula Breakdown**

**SEARCH(“*”&$H$5&”*”,D5)**→ returns the location of a text string inside another one.**“*”&$H$5&”*”**→ is theargument.*find_text***D5**→ is theargument.*within_text***Output**→**1**.

**ISNUMBER(SEARCH(“*”&$H$5&”*”,D5))**→ becomes**ISNUMBER(1)**.**Output**→**TRUE**.

- In the
**IF**function,**ISNUMBER(SEARCH(“*”&$H$5&”*”,D5))**→ is theargument.*logical_test***“Desired State”**→ is theargument.*[value_if_true]***“It’s another State”**→ is theargument.*[value_if_false]***Output**→**Desired State**.

- Press
**ENTER**.

You will see the following output in **F5**:

- Drag down the Fill Handle to see the result in the rest of the cells.

## Method 3 – Merging the IF, ISNA, and MATCH Functions to get a Partial Match

__Steps:__

- Enter the following formula in
**F5**.

`=IF(ISNA(MATCH("*New*",D5,0)),"Not Found!","Found")`

**Formula Breakdown**

**MATCH(“*New*”,D5,0)**→ returns the relative position of a specified lookup value.- Here,
**“*New*”**→ is theargument.*lookup_value* **D5**→ is theargument.*lookup_array***0**→ is theargument.*[match_type]***Output**→**1**.

- Here,
**ISNA(MATCH(“*New*”,D5,0))**→ becomes**ISNA(1)**.**Output**→**FALSE**.

- In the
**IF**function,**ISNA(MATCH(“*New*”,D5,0))**→ is theargument.*logical_test***“Not Found!”**→ is theargument.*[value_if_true]***“Found”**→is theargument.*[value_if_false]***Output**→**Found**.

- Press
**ENTER**.

You will see the following output in **F5**:

- Drag down the Fill Handle to see the result in the rest of the cells.

## Method 4 – Applying the OR Operation with the IF Function to get a Partial Match

__Steps:__

- Enter the following formula in
**E5**.

`=IF(OR(ISNUMBER(SEARCH("*"&$F$5&"*",D5)),ISNUMBER(SEARCH($G$5,C5))),"Contact","")`

**F5 **indicates the **Search State**, **G5 **refers to the **Number Ext.**, and **C5 **is the first cell in the **Phone **column.

**Formula Breakdown**

- The
**two**logical arguments of the**OR**function are:**ISNUMBER(SEARCH(“*”&$F$5&”*”,D5))**→ returns**TRUE**.**ISNUMBER(SEARCH($G$5,C5))**→ returns**TRUE**.

**OR(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ becomes**OR(TRUE,TRUE)**.**Output**→**TRUE**.

- In the
**IF**function,**OR(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ is theargument.*logical_test***“Contact”**→ is theargument.*[value_if_true]***“”**→ is theargument.*[value_if_false]***Output**→**Contact**.

- Press
**ENTER**.

You will see the following output in **E5**:

- Drag down the Fill Handle to see the result in the rest of the cells.

## Method 5 – Using the AND Operation with an Excel IF Function to get a Partial Match

__Steps:__

- Enter the following formula in
**E5**.

`=IF(AND(ISNUMBER(SEARCH("*"&$F$5&"*",D5)),ISNUMBER(SEARCH($G$5,C5))),"Contact","")`

**Formula Breakdown**

- The
**two**logical arguments of the**AND**function are:**ISNUMBER(SEARCH(“*”&$F$5&”*”,D5))**→ returns**TRUE**.**ISNUMBER(SEARCH($G$5,C5))**→ returns**TRUE**.

**AND(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ becomes**AND(TRUE,TRUE)**.**Output**→**TRUE**.

- In the
**IF**function,**AND(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5))**→ is theargument.*logical_test***“Contact”**→ is theargument.*[value_if_true]***“”**→ is theargument.*[value_if_false]***Output**→**Contact**.

- Press
**ENTER**.

You will see the following output in **E5**, as both logical arguments return **TRUE**:

- Drag down the Fill Handle to see the result in the rest of the cells.

## Method 6 – Finding a Partial Match using a Question Mark

__Steps:__

- Enter the following formula in
**F5**.

`=IF(ISNA(MATCH("*C?bleskill*",D5,0)),"Not Found!","Found")`

**Formula Breakdown**

**MATCH(“*C?bleskill*”,D5,0)**→ returns the relative position of a specified lookup value.**“*C?bleskill*”**→ is theargument.*lookup_value***D5**→ is theargument.*lookup_array***0**→ is theargument.*[match_type]***Output**→**#N/A**.

**ISNA(MATCH(“*C?bleskill*”,D5,0))**→ becomes**ISNA(#N/A)**.**Output**→**TRUE**.

- In the
**IF**function,**ISNA(MATCH(“*C?bleskill*”,D5,0))**→ is theargument.*logical_test***“Not Found!”**→ is theargument.*[value_if_true]***“Found”**→ is theargument.*[value_if_false]***Output**→**Not Found!**.

- Press
**ENTER**.

This is the output.

- Drag down the Fill Handle to see the result in the rest of the cells.

A partial match was found in **D8 **and the correct spelling is **Cobleskill**.

## How to Find a Partial Match in Two Columns in Excel

The dataset contains **Characters** and** Nicknames**.

__Steps:__

- Enter the following formula in
**D5**.

`=IFERROR(VLOOKUP("*"&C5&"*",$B$5:$B$10,1,0),"")`

**C5 **indicates the first cell in the **Nickname **column, and** B5:B10** refers to the cells in the **Character Name **column.

**Formula Breakdown**

- The
**VLOOKUP**function returns the value from the specified column if the value matches the lookup value. - In the
**VLOOKUP(“*”&C5&”*”,$B$5:$B$10,1,0)**function,**“*”&C5&”*”**→ is theargument.*lookup_value***$B$5:$B$10**→ is theargument.*table_array***1**→ is theargument.*col_index_num***0**→ is theargument.*[range_lookup]***Output**→**“Jon Snow”**.

- The
**IFERROR**function returns a specific value for the error criteria. **IFERROR(VLOOKUP(“*”&C5&”*”,$B$5:$B$10,1,0),””)**→ becomes**IFERROR(“Jon Snow”,””)**.**“Jon Snow”**→ is theargument.*value***“”**→ is theargument.*value_if_error***Output**→**Jon Snow**.

- Press
**ENTER**.

This is the output.

- Drag down the Fill Handle to see the result in the rest of the cells.

**Download Practice Workbook**

**<< Go Back to Partial Match Excel | Formula List | Learn Excel**