compare two columns in excel to find missing data

lookup_value ( what do you want Excel to lookup or find).MATCH has three required arguments, or inputs, to work:

The conditional formatting just makes it easier to spot the missing values. If MATCH does not find a value from List A within List B, it’ll return #N/A and ISNA will display TRUE. Whenever MATCH returns the relative position and not #N/A, ISNA simply spits out FALSE. If MATCH finds a value from List A within List B, it’ll return the relative position of the value in List B. In our example we are using MATCH to tell ISNA whether to display TRUE or 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.

If a value is #N/A in an ISNA formula, it’ll display TRUE. ISNA is the first function in our formula, which does only one thing in life: checks whether a value is #N/A (which is the error value Excel uses to say “no value is available”). You could take it one step farther by filtering either list to display only TRUE which would then only show the values missing from either list. TRUE = value is missing from the other listįALSE = value is not missing from the other list If any value from List A is found in List B, FALSE will appear next to that value in List A’s “Missing?” column. 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.

Repeat steps four through six for cell E5, making sure to copy the formula down the entire List B.

In cell E5, enter the formula: =ISNA(MATCH(D5,A:A,0)) and press Enter.

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 the Text That Contains dialogue box, type TRUE and click OK.

Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains….

In cell B5 enter the formula: =ISNA(MATCH(A5,D:D,0)) and press Enter.

Label the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”.

I chose column A for List A and column D for List B.

Arrange the lists in columns, leaving at least two blank columns in between.

To do this we’ll write a formula using the ISNA and MATCH functions. We want to know if any account values from List A are missing from List B, and vice versa. In the example below, we have two large lists of four digit account values: List A and List B. 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. For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other.

Compare two columns in excel to find missing data