People compare lists (or columns) of information all the time, and Excel provides several easy ways to it. In fact Microsoft sums up several real life examples quite nicely:
For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other. Which new clients need to be added to the master database? Which credit card receipts are not reflected on the monthly statement, and vice versa? What checks and deposits are still outstanding? It’s a tedious task if you do it manually.
In the example below we have two large lists of four digit account values: List A and List B. We want to know if any account values from List A are missing from List B, and vice versa. To do this we’ll write a formula using the
How to Do It
- Arrange the lists in columns, leaving at least two blank column in between. I chose column A for List A and column D for List B.
- Name the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”
- In cell B5 enter the formula:
=ISNA(MATCH(A5,D:D,0))and press Enter.
- Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…
- In the Text That Contains dialog box, type TRUE and click OK.
- Select cell B5 down to the end of List A and press Ctrl+D to copy the formula and conditional formatting down the entire List A.
- In cell E5, enter the formula:
=ISNA(MATCH(D5,A:A,0))and press Enter.
- Repeat steps 4-6 for cell E5, making sure to copy the formula down the entire List B.
If any value from List A is missing from List B, a red TRUE will appear next to that value in List A’s “Missing?” column. If any value from List A is found in List B, FALSE will appear next to that value in List A’s “Missing?” column.
A TRUE = value is missing from the other list
A FALSE = value is not missing from the other list
You could take it one step farther by filtering either list to display only TRUE which would then only display the values missing from either list.
Done and done.
How and Why It Works
ISNA is the first function in our formula, which does only one thing in life: checks whether a value is #N/A (the error value Excel uses to say “no value is available”). If a value is #N/A in an
ISNA formula, it’ll display TRUE. Otherwise it’ll display FALSE.
MATCH is the second function, which returns the relative position of an item in an array (in our example, either List A or List B) that matches a specified value in a specified order. In our example we are using
MATCH to tell
ISNA whether to display TRUE or FALSE.
MATCH finds a value from List A within List B, it’ll return the relative position of the value in List B. Whenever
MATCH returns the relative position and not #N/A,
ISNA simply spits out FALSE. If
MATCH does not find a value from List A within List B, it’ll return #N/A and
ISNA will display TRUE.
The conditional formatting just makes it easier to spot the missing values.
MATCH has three required arguments, or inputs, to work:
lookup_value(what do you want Excel to lookup or find)
lookup_array(where should Excel look. In our example, A:A and D:D selects those entire columns)
match_type(what type of a match Excel should find: the next largest, an exact match only, or the next smallest)
ISNA literally checks whether the cell value “IS #N/A,” or “IS NO VALUE AVAILABLE?”
- If it IS #N/A, it’ll display TRUE.
- If it IS NOT #N/A, it’ll display FALSE.
The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!