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:

1. Select cell D2.
2. Type the formula: =IFERROR(VLOOKUP(“*”&C2&”*”, $B$2:$B$10, 1, 0), “”)
3. Press ENTER to see the result.
4. 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:

1. Select cell D2.
2. Type the formula: =IFERROR(VLOOKUP(“*”&C2&”*”, $B$2:$B$10, 1, 0), “”)
3. Press ENTER to insert the formula.
4. 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$10value.

• 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:

1. Select cell D2.
2. Type the formula: =IFERROR(INDEX($B$2:$B$10, MATCH(“*” & C2 & “*”, $B$2:$B$10, 0)), “”)
3. Press ENTER to insert the formula.
4. 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:

1. Choose cell D2.
2. Type the formula: =IF(COUNTIF(B$2:B$10, “*” & C2 & “*”) > 0, INDEX(B$2:B$10, MATCH(“*” & C2 & “*”, B$2:B$10, 0)), “”)
3. To insert the formula, press ENTER.
4. 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:

1. Press ALT+F11 to open Visual Basic Editor.
2. Choose Insert > Module.
3. 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

4. Save the code by pressing CTRL+S.
5. Click the Macros button under Developer.
   Or, Access the Macro dialog box by pressing ALT+F8.
6. Select FindPartialMatchWithUserinput and then click Run.
7. Select the range C2:C10 and click OK.
8. Click OK after selecting the range B2:B10.
9. 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:

1. Select the cells in column B where you want to apply the formatting.
2. Go to the “Home” tab on the Ribbon.
3. Click on “Conditional Formatting” and choose “New Rule.”
4. Select “Use a formula to determine which cells to format.”
5. Enter the formula =ISNUMBER(SEARCH(A1, B1))
6. 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:

1. Select cell C1.
2. Type the formula: =XLOOKUP(“*” & A1 & “*”, B:B, B:B, “No Match”, 1, 1)
3. 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:

1. Choose cell C1.
2. Write the formula: =IF(ISNUMBER(VLOOKUP(A1, B:B, 1, FALSE)), “Match”, “No Match”)
3. 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(…)).