How to Highlight Values That Exist in List1 and List2 in Excel

Sometimes, you may have two lists of values in Excel and you want to highlight the values that are common to both lists. 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 from you in both months.

There are different ways to achieve this task in Excel, but one of the simplest and most flexible methods is to use a conditional formatting formula. In this article, we will explain how to use this method and show you a detailed example with real data.

Conditional formatting is a feature in Excel that allows you to apply different formats (such as colors, fonts, icons, etc.) to cells based on certain criteria or conditions. You can use conditional formatting to highlight, emphasize, or visualize data in various ways.

To use conditional formatting, you need to specify a range of cells that you want to format, and a rule that defines the condition and the format. You can create rules based on cell values, formulas, dates, text, or other criteria.

In our case, we want to create a rule based on a formula that checks if a value in List1 exists in List2. To do this, we can use the MATCH function, which returns the relative position of a value in a range or array. For example, =MATCH(10,A1:A5,0) returns 3, because 10 is the third value in the range A1:A5.

The MATCH function has three arguments: the value to look for, the range or array to look in, and the match type. The match type can be 0, 1, or -1, depending on whether you want an exact match, a less than match, or a greater than match. In our case, we want an exact match, so we use 0 as the match type.

If the MATCH function finds a match, it returns a positive number. If it does not find a match, it returns an error value, such as #N/A. We can use the ISNUMBER function to check if the result of the MATCH function is a number or not. The ISNUMBER function returns TRUE if the argument is a number, and FALSE otherwise.

Therefore, we can use the following formula to check if a value in List1 exists in List2:

=ISNUMBER(MATCH(List1_value,List2_range,0))

This formula returns TRUE if the value in List1 is found in List2, and FALSE otherwise. We can use this formula as the condition for our conditional formatting rule.

Procedures

To apply conditional formatting based on the formula above, follow these steps:

1. Select the range of cells that contains List1. For example, if List1 is in column A, select A1:A10.
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 that checks if a value in List1 exists in List2. For example, if List2 is in column B, enter =ISNUMBER(MATCH(A1,B$1:B$10,0)). Note that we use absolute references for the List2 range, so that it does not change when the formula is copied to other cells in List1.
5. Click Format, and then choose the format that you want to apply to the matching values. For example, you can choose a fill color, a font color, a border, etc. Click OK to close the Format Cells dialog box.
6. Click OK to close the New Formatting Rule dialog box. You should see the values that exist in both lists highlighted with the format that you chose.

Example

To illustrate the above method, let’s use a sample data set that contains two lists of customers who bought products from a company in January and February. The data set looks like this:

We want to highlight the customer IDs that bought products in both months. To do this, we follow the steps above:

1. Select the range of cells that contains the customer IDs, which is A2:A11.
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 that checks if a customer ID exists in both lists. In this case, the formula is =ISNUMBER(MATCH(A2,B$2:B$11,0))*ISNUMBER(MATCH(A2,C$2:C$11,0)). Note that we use the * operator to combine the two MATCH functions, so that the formula returns TRUE only if both conditions are met. Also note that we use absolute references for the January and February ranges, so that they do not change when the formula is copied to other cells in the customer ID range.
5. Click Format, and then choose the format that you want to apply to the matching values. For example, you can choose a green fill color and a white font color. Click OK to close the Format Cells dialog box.
6. Click OK to close the New Formatting Rule dialog box. You should see the customer IDs that bought products in both months highlighted with the format that you chose.

The result should look like this:

Other Approaches

The method that we have shown above is not the only way to highlight values that exist in List1 and List2 in Excel. Here are some other possible approaches that you can try:

• You can use the COUNTIF function instead of the MATCH function to check if a value in List1 exists in List2. The COUNTIF function counts the number of cells in a range that meet a given criterion. For example, =COUNTIF(B1:B10,A1) returns the number of cells in B1:B10 that are equal to A1. You can use the following formula as the condition for your conditional formatting rule:

=COUNTIF(List2_range,List1_value)>0

This formula returns TRUE if the value in List1 is found in List2, and FALSE otherwise.

• You can use the VLOOKUP function instead of the MATCH function to check if a value in List1 exists in List2. The VLOOKUP function looks up a value in the first column of a table and returns the value in the same row from another column. For example, =VLOOKUP(10,A1:B5,2,FALSE) returns the value in the second column of the table A1:B5 that corresponds to the value 10 in the first column. You can use the following formula as the condition for your conditional formatting rule:

=NOT(ISNA(VLOOKUP(List1_value,List2_range,1,FALSE)))

This formula returns TRUE if the value in List1 is found in List2, and FALSE otherwise. The ISNA function checks if the result of the VLOOKUP function is an error value, such as #N/A. The NOT function reverses the logical value, so that TRUE becomes FALSE and vice versa.

• You can use the AND function to combine multiple conditions for your conditional formatting rule. For example, if you want to highlight the values that exist in List1, List2, and List3, you can use the following formula as the condition:

=AND(ISNUMBER(MATCH(List1_value,List2_range,0)),ISNUMBER(MATCH(List1_value,List3_range,0)))

This formula returns TRUE if the value in List1 is found in both List2 and List3, and FALSE otherwise. The AND function returns TRUE if all the arguments are TRUE, and FALSE if any of the arguments are FALSE.