Home > Formulas > VLOOKUP > Troubleshoot Compare Cell Values in ExcelTroubleshootIn an Excel worksheet, a VLOOKUP formula might give an error, because two cells that look the same, are not really equal. Here are ways to compare cell values to troubleshoot the problem. Use formulas to check for exact match or partial matches. Author: Debra Dalgleish 
Introduction: Compare Cell ValuesHow can you compare two cells in Microsoft Excel, to see if they are exactly the same, or partially match? On this page, there are a few functions and formulas that check the contents of two cells, to see if they are the same. In the sections below, we'll start with a simple check of the cell contents. After that, we'll try different tests, for more complex comparisons of the contents of two cells on an Excel worksheet. 1) Quick Tests to Compare Two CellsDo you ever have problems with the VLOOKUP function, like the one shown in the screen shot below?
There are 3 quick tests, shown below, that I usually start with, when I need to compare two cells on a worksheet. 
Test A) Are 2 Cells Equal?First, I use the equal sign in a formula, to compare the two cells. For example, in the next screen shot, I entered the following formula in cell A9:
The result of that formula is a Boolean value  TRUE or FALSE
Note: Upper and lower case letters are treated as equal 
Test B) Check the LengthNext, I'll use the Excel LEN function, to see if the two cell values are the same length. Sometimes there are extra spaces in a cell, at the start, or at the end, or between words. I entered the following formulas, to check the lengths:
The result for both formulas is 3, so there are no extra characters in one cell, and not in the other cell. Formula Fix  Extra SpacesTo fix a VLOOKUP formula, so it will ignore extra spaces, you can use the TRIM function inside the VLOOKUP. For detailed step, see this VLOOKUP example on my Contextures Blog. In the screen shot below, the VLOOKUP formula includes TRIM, to remove extra spaces, and SUBSTITUTE, to remove the leading slash character, in the lookup value. 
Test C) Check for NumbersFor my third test, I'll use the ISNUMBER function, to see if the two cell values are real numbers. Sometimes cells contain "text" numbers, instead of real numbers, especially if the data was imported, or copied from a website. To check, I entered the following formulas, to test for numberss:
The results for the above formulas show that the:
A real number is not a match for a text number. That's why Excel couldn't find matching data for product code 123 (real number) in the lookup table, and returned an #NA error. Formula Fix  Real Numbers and Text NumbersIn the video below, I show how to change a VLOOKUP formula, so it will fix the mismatched data problem of real numbers vs text numbers. There are written steps on the VLOOKUP Examples page. 
Video: VLOOKUP Formula FixTo see the steps for fixing VLOOKUP formula when the lookup table has text numbers that cause a mismatch, watch this short video. There are written steps on the VLOOKUP Examples page. Video Timeline

2) Compare Two Cells ExactlyIf you need to compare two cells for contents, including a case sensitive comparison of letters, use the EXACT function. As its name indicates, the EXACT function can check for an exact match between text strings, including upper and lower case.
Formatting: This function doesn’t test the cell formatting though, so it won’t compare how you format cells. If one cell has some or all of the characters in bold, and the other cell doesn’t, the EXACT function could still return TRUE. Video: EXACT Function ExamplesThis video shows a few more EXACT function examples. For each example shown in the video, you'll find the written steps for each example on the EXACT Function Examples page. You can download the sample file there too. 
Partially Compare Two CellsSometimes you don’t need a full comparison of two cells – you just need to check the first few characters at the left, or a 3digit code at the right end of a string. The following examples use the LEFT and RIGHT functions to compare partial text in two different cells. Compare from LeftTo compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:
Compare from RightTo compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:
Ignore Extra Space CharactersYou can combine LEFT or RIGHT with TRIM, if you’re not concerned about the space characters:
And combine LEFT or RIGHT with EXACT, to check if upper/lower case match too. This formula will ignore extra spaces, but checks the case:

5) Find Percent 2 Cells MatchFinally, here’s a formula from UniMord, that checks how much of a match there is between two cells. UniMord created this formula for a project at work, where he needed to compare old and new addresses. In the screen shot below, there’s a sample list, where the addresses in column A and B and being compared, with formulas starting in column C. Each formula is in a separate column, instead of create a long, complex formula in a single column. Percent Match FormulasWith formulas in columns C, D and E, we can calculate a percentage match, by comparing the address in column B to the original address in column A. Here's what the 3 formulas do, and the formula details are shown in the sections below:
Col C: Get Text LengthThe first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:
Col D: Get Match LengthThe formula in column D is doing the hard work. It finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared. Note: There are 2 formulas shown below one for Excel 365, and one for earlier versions of Excel A) For Excel 365, or other versions that have the new Spill Functions, use this formula:
B) For earlier versions of Excel, use this formula  it will work in any version of Excel:

How the Formulas WorkThe example in the screen shot below shows a summary of the steps.
For more details on how these two Match Length formulas work, go to the How Match Len Formula Works section below. Col E: Get the Percent MatchOnce the text length and the match length have been calculated, it just takes a simple formula to find the percent matched. Here is the result column formula from cell E2, to compare the two lengths:
There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5. 
How Match Len Formula WorksNote: You can skip reading this section, unless you want more details on how the Match Length formulas work. In column D, the Match Len formula compares the 2 cells, to see how many characters match, starting from the left. To get the result, the formula does 4 things:
1. Get Cell CharactersTo find out how many characters have an equal match, the formula will use the LEFT function to:
Usually, we enter a single numbers as the second argument for the LEFT function. However, for this formula, we want the LEFT function to check a list of numbers, from 1 to X. If we typed the list of numbers in the formula, the numbers would be in an array, like this:

Create Array of Numbers with FormulaWe don't want to type an array of numbers into the formula though. We want Excel to automatically create a list of numbers, starting with 1, and ending at X. (X is the length of Address01, in this example) There are two formulas shown below, so use that one that works in your version of Excel: A) Array of Numbers  Excel 365Use this shorter formula, in Excel 365, or other versions that have the new Spill Functions. In it, the SEQUENCE function creates the list of numbers:
B) Array of Numbers  All Versions of ExcelFor earlier versions of Excel, use this longer formula. It will work in any version of Excel, and uses two functions to create the list of numbers  ROW and INDIRECT:
Here's how the ROW and INDIRECT functions work together, to create the list of numbers:
In the screen shot below, you can see the list of numbers that the two functions create. To make that list appear, follow these steps: 
2) Are Cell Character Strings Equal?After the LEFT function gets character strings from each cell, the equal sign compares those stings.
In the screen shot below, there were 5 character strings to compare. The first result is TRUE, and the remaining four results are FALSE. 3) Change TRUE and FALSE to NumbersNext, the double minus sign, near the start of the formula, converts:
4) Add the NumbersFinally, the SUMPRODUCT function adds up those numbers, to calculate the total number of characters, from the left, that match. In cell D5, in the screen shot below, the total is 1.

More Ways to Compare Two CellsHere are a few more articles that show examples of how to compare two cells – either the full content, or partial content.
__________________________ Download the WorkbookCompare Cells: Download the sample file with Compare Cells examples from this tutorial. The zipped Excel file is in xlsx format, and does not contain macros. 
Related Tutorials 
Last updated: September 13, 2023 12:34 PM