# 4 Ways to Find Partial Match in Two Columns in Excel

**To find a partial match in two columns in Excel, follow the steps below:**

- Select cell
**D2**. - Type the formula:
**=IFERROR(VLOOKUP(“*”&C2&”*”, $B$2:$B$10, 1, 0), “”)** - Press
**ENTER**to see the result. - Drag the
**File Handle**to copy down the formula.

This formula searches for a partial match of the text in cell **C2** within a range in Column **B** and returns the exact matching value from Column** B**, or an empty cell if no match is found.

## Find Partial Match in Two Columns Using Vlookup Function in Excel

Data in a vertically arranged range are found using the **VLOOKUP** function. To find partial matches I will compare two columns (columns **B** and **C**). Then I will keep the matched data in column **D**.

**Syntax**

=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), value_if_error)

**Formula**

=IFERROR(VLOOKUP("*"&C2&"*", $B$2:$B$10, 1, 0), "")

**Formula Breakdown**

The **VLOOKUP** function looks for **“Takkos”** within the range **$B$2:$B$10** at any position using wildcard characters **(*)**. If it finds any, then it directly pulls it otherwise it returns #N/A! error. Finally, the **IFERROR** function replaces the **#N/A!** error with an empty string.

Follow these steps below to find a partial match in two columns in Excel using **VLOOKUP** function:

- Select cell
**D2.** - Type the formula:
**=IFERROR(VLOOKUP(“*”&C2&”*”, $B$2:$B$10, 1, 0), “”)** - Press
**ENTER**to insert the formula. - Drag the
**Fill Handle**from**D2**to**D10.**

** **

We have found partial matches against all the names except **James** and **Arvyy**. **James** doesn’t exist within **B2: B10** and **Arvyy** is misspelled.

## Find Partial Match in Two Columns with INDEX and MATCH Functions in Excel

**INDEX** and** MATCH **functions allow us to find specific information in one column and then get related data from another column, even when the match is only a partial one.

**Syntax**

**=IFERROR(INDEX(array, MATCH(lookup_value,lookup_array, [match_type]),[column_number),value_if_error )**

**Formula**

=IFERROR(INDEX($B$2:$B$10, MATCH("*" & C2 & "*", $B$2:$B$10, 0)), "")

**Formula Breakdown**

This formula searches for the text in cell **C2** within the range **$B$2:$B$10**value.

- Asterisks
**(*)**, which serve as a wildcard for finding partial matches, are added before and after the text in cell**C2**to search for it. - If it finds a match, it returns the corresponding value from column
**B**. When it doesn’t find a match, it leaves the cell empty instead of showing an error. Because the**IFERROR**function replaces the errors with a null value.

To find partial matches in two columns, you can use the** INDEX** and **MATCH** functions. Here’s how:

- Select cell
**D2.** - Type the formula:
**=IFERROR(INDEX($B$2:$B$10, MATCH(“*” & C2 & “*”, $B$2:$B$10, 0)), “”)** - Press
**ENTER**to insert the formula. - Use the
**Fill Handle**to drag the formula down.

James isn’t present in the** B2:B10 **column and Arvyy has a spelling mistake. So, this formula gives empty cells for these names.

## Get Partial Match in Two Columns Using IF and COUNTIF Functions in Excel

With the** IF** function, we can create conditions and determine the value to return based on whether those conditions are fulfilled. When used in combination with the **COUNTIF** function,

we can count or locate cells within a range that only partially matches.

**Syntax**

**=IF(COUNTIF(range,criteria) > 0, INDEX(array, MATCH(lookup_value,lookup_array, [match_type])), value_if_false)**

**Formula**

**=IF(COUNTIF(B$2:B$10, "*" & C2 & "*") > 0, INDEX(B$2:B$10, MATCH("*" & C2 & "*", B$2:B$10, 0)), "")**

**Formula Breakdown**

The **COUNTIF** function first searches for **“Takkos”** in the range **B2:B10**.

- If the count value is more than
**0**(which confirms the existence of**“Takkos”**within**B2:B10**), then it returns the matched result using the following part of the formula:**INDEX(B$2:B$10, MATCH(“*” & C2 & “*”, B$2:B$10, 0))**. - If the count value of
**“Takkos”**within the range**B2:B10**is zero, the formula returns an empty string.

To find a partial match in two columns, you can use the combination of **IF** and **COUNTIF** functions. Here’s how:

- Choose cell
**D2.** - Type the formula:
**=IF(COUNTIF(B$2:B$10, “*” & C2 & “*”) > 0, INDEX(B$2:B$10, MATCH(“*” & C2 & “*”, B$2:B$10, 0)), “”)** - To insert the formula, press
**ENTER.** - Drag the formula down with the
**Fill Handle.**

** **

The formula searches for partial matches in columns **B** and** C** . Empty cells indicate that no partial matches were found.

** **

## Find Partial Match in Two Columns Using VBA Code in Excel

Using the programming language **VBA** in Microsoft Excel, you may automate processes, develop unique functions, and create applications. We also find partial matches in two columns using **VBA** code in Excel.

Follow these steps to find partial match in two columns with the help of **VBA**:

- Press
**ALT+F11**to open**Visual Basic Editor**. - Choose
**Insert****>****Module**. **Copy**the following**code**and**Paste**it to the newly created module.`Sub FindPartialMatchesWithUserInput() Dim searchRange As Range Dim matchRange As Range Dim outputColumn As Range Dim cellA As Range, cellB As Range Dim partialMatch As Boolean ' Prompt the user to select the range to search in On Error Resume Next Set searchRange = Application.InputBox("Select the range to search in:", Type:=8) On Error GoTo 0 ' Prompt the user to select the range to match against On Error Resume Next Set matchRange = Application.InputBox("Select the range to match against:", Type:=8) On Error GoTo 0 ' Check if both ranges were selected If searchRange Is Nothing Or matchRange Is Nothing Then MsgBox "Both ranges must be selected.", vbExclamation Exit Sub End If ' Prompt the user to select the column where output should be displayed On Error Resume Next Set outputColumn = Application.InputBox("Select the output column:", Type:=8) On Error GoTo 0 ' Check if the output column was selected If outputColumn Is Nothing Then MsgBox "An output column must be selected.", vbExclamation Exit Sub End If ' Loop through each cell in the search range For Each cellA In searchRange partialMatch = False ' Reset the flag for each cell in the search range ' Loop through each cell in the match range For Each cellB In matchRange ' Check if cellB contains a partial match of the value in cellA If InStr(1, cellB.Value, cellA.Value, vbTextCompare) > 0 Then partialMatch = True ' Display the matching value from the match range in the output column outputColumn.Cells(cellA.Row - searchRange.Cells(1).Row + 1, 1).Value = cellB.Value Exit For ' Exit the loop if a match is found End If Next cellB Next cellA End Sub`

- Save the code by pressing
**CTRL+S.** - Click the
**Macros**button under**Developer**.

Or, Access the**Macro**dialog box by pressing**ALT+F8**. - Select
**FindPartialMatchWithUserinput**and then click**Run**. - Select the range
**C2:C10**and click**OK.** - Click
**OK**after selecting the range**B2:B10**. - Select cell
**D2**and click**OK.**

The **VBA **code displays results in column **D2:D10** when it identifies a partial match. If there’s no match, the cell remains empty.

## Conclusion

In this article, I’ve shown 4 different methods to find partial matches in two columns in Excel. It returns the matched result when there is a partial match. On the other hand, it retains an empty cell when there are some errors or no match is found.

## Frequently Asked Questions

### What is a partial match in Excel?

In Excel, a partial match typically refers to finding occurrences of a specific substring within a larger text string. This concept is often used when you want to search for a portion of text within a cell’s contents rather than the entire content.

### How do I compare two columns in Excel for a partial match?

You can compare two columns in Excel for a partial match using **Conditional Formatting**. Here’s how:

- Select the cells in column
**B**where you want to apply the formatting. - Go to the
**“Home”**tab on the Ribbon. - Click on
**“Conditional Formatting”**and choose**“New Rule.”** - Select
**“Use a formula to determine which cells to format.”** - Enter the formula
**=ISNUMBER(SEARCH(A1, B1))** - Set the formatting style you prefer.

This will highlight cells in column **B** where the text in column **A** is partially matched.

### Can you use Xlookup for partial matches?

Yes, **XLOOKUP** is a powerful function in Excel that can be used for partial matches. Here’s how you can use **XLOOKUP **for partial matches:

- Select cell
**C1.** - Type the formula:
**=XLOOKUP(“*” & A1 & “*”, B:B, B:B, “No Match”, 1, 1)** - Press
**ENTER**to insert the formula.

Simply enter this formula in a cell, drag it down for the entire column, and the results will display corresponding values from column** B** where partial matches are detected in column **A**.

Keep in mind that **XLOOKUP** is available in Excel 365 and Excel 2019.

### Can I use VLOOKUP to compare two columns?

Yes, you can use **VLOOKUP** to compare two columns in Excel.

Here’s a simple example of how you can use VLOOKUP to compare two columns and identify matches in a third column:

- Choose cell
**C1.** - Write the formula:
**=IF(ISNUMBER(VLOOKUP(A1, B:B, 1, FALSE)), “Match”, “No Match”)** - Press
**ENTER**to insert the formula.

Keep in mind that **VLOOKUP** is limited to exact matches. If you need to check for partial matches or more advanced comparison criteria, you might want to explore other functions like **INDEX** and **MATCH **or consider using helper columns with formulas like **IF** and **ISNUMBER(SEARCH(…))**.