How to Highlight Values That Exist in List1 but not List2 in Excel

Sometimes, you may have two lists of values in Excel and you want to highlight the values that are in one list but not in the other. For example, you may have a list of customers who bought products from you in January and another list of customers who bought products from you in February. You may want to highlight the customers who bought products in January but not in February, or vice versa.

There are different ways to achieve this task in Excel, but one of the simplest and most effective methods is to use conditional formatting with a formula. Conditional formatting is a feature that allows you to apply different formats (such as colors, fonts, icons, etc.) to cells based on certain criteria or conditions. A formula is an expression that calculates a value or performs a logical test.

In this article, we will explain how to use conditional formatting with a formula to highlight values that exist in List1 but not List2 in Excel. We will also provide a detailed example with real data and show you some other approaches that you can use.

The basic idea behind using conditional formatting with a formula to highlight values that exist in List1 but not List2 is to use the MATCH function. The MATCH function returns the relative position of a value in a range or array. For example, =MATCH(10,A1:A5,0) will return 3, because 10 is the third value in the range A1:A5.

The MATCH function has three arguments: lookup_value, lookup_array, and match_type. The lookup_value is the value that you want to find in the lookup_array, which is the range or array that you want to search. The match_type is an optional argument that specifies how you want to match the lookup_value. If you omit the match_type or set it to 0, the MATCH function will perform an exact match, meaning that it will only return a position if the lookup_value is exactly equal to a value in the lookup_array. If you set the match_type to 1, the MATCH function will perform a less than match, meaning that it will return the largest position where the lookup_value is less than or equal to a value in the lookup_array. If you set the match_type to -1, the MATCH function will perform a greater than match, meaning that it will return the smallest position where the lookup_value is greater than or equal to a value in the lookup_array.

The MATCH function will return a number if it finds a match, or an error value (#N/A) if it does not find a match. We can use this behavior to create a formula that will return TRUE if a value exists in List1 but not in List2, or FALSE otherwise. The formula is:

=ISNA(MATCH(value,List2,0))

This formula will check if the value is in List2 by using the MATCH function with an exact match (match_type = 0). If the value is in List2, the MATCH function will return a number, and the ISNA function will return FALSE. If the value is not in List2, the MATCH function will return an error value (#N/A), and the ISNA function will return TRUE. The ISNA function checks if a value is an error value (#N/A).

We can use this formula as the condition for the conditional formatting rule. The conditional formatting rule will apply a format (such as a color) to the cells that meet the condition (TRUE), and leave the cells that do not meet the condition (FALSE) unchanged.

Procedures

To use conditional formatting with a formula to highlight values that exist in List1 but not List2 in Excel, follow these steps:

1. Select the cells that contain List1. You can select a single cell, a range of cells, a column, a row, or the entire worksheet.
2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.
3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter the formula =ISNA(MATCH(value,List2,0)), where value is the cell reference of the first cell in List1, and List2 is the range or array that contains List2. For example, if List1 is in column A and List2 is in column B, you can enter the formula =ISNA(MATCH(A1,B:B,0)).
5. Click Format to choose the format that you want to apply to the cells that meet the condition. You can choose from different options such as Fill, Font, Border, or Number. For example, you can choose a yellow fill color to highlight the cells.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box. You will see that the cells that contain values that exist in List1 but not List2 are highlighted with the format that you chose.

Example

To illustrate how to use conditional formatting with a formula to highlight values that exist in List1 but not List2 in Excel, let us consider the following example:

Suppose you have two lists of products that you sell in your online store: List1 contains the products that you sold in January, and List2 contains the products that you sold in February. You want to highlight the products that you sold in January but not in February, to see which products are losing popularity or demand.

The data is shown in the following table:

To highlight the products that exist in List1 but not List2, we can use the following steps:

1. Select the cells that contain List1, which are A1:A26 in this example.
2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.
3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter the formula =ISNA(MATCH(A1,B:B,0)), where A1 is the first cell in List1, and B:B is the column that contains List2.
5. Click Format to choose the format that you want to apply to the cells that meet the condition. For example, you can choose a yellow fill color to highlight the cells.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box. You will see that the cells that contain products that exist in List1 but not List2 are highlighted with the yellow color, as shown in the following table:

The result of the example is that the product Z is the only product that exists in List1 but not List2, and it is highlighted with the yellow color.

Other Approaches

Besides using conditional formatting with a formula, there are some other approaches that you can use to highlight values that exist in List1 but not List2 in Excel. Here are some of them:

• You can use the VLOOKUP function to look up the values in List1 in List2, and return an indicator value (such as 1 or 0) if they are found or not. Then, you can use conditional formatting based on the indicator values to highlight the cells. For example, you can enter the formula =IFERROR(VLOOKUP(A1,B:B,1,0),0)

• You can use the COUNTIF function to count how many times the values in List1 appear in List2, and return a number (such as 0 or 1) if they are found or not. Then, you can use conditional formatting based on the number values to highlight the cells. For example, you can enter the formula =COUNTIF(B:B,A1)
• You can use the ISNUMBER function to check if the values in List1 are numbers or not, and return a logical value (such as TRUE or FALSE) if they are or not. Then, you can use conditional formatting based on the logical values to highlight the cells. For example, you can enter the formula =ISNUMBER(A1)
• You can use the FILTER function to filter the values in List1 that are not in List2, and return an array of the filtered values. Then, you can use conditional formatting based on the array values to highlight the cells. For example, you can enter the formula =FILTER(A:A,ISNA(MATCH(A:A,B:B,0)))