If you are dealing with two lists in Google Sheets and you want to highlight the differences between them, you can use conditional formatting. In this article, we will show you how to use the VLOOKUP function with a formula to highlight new customers in a February list that aren’t present in the January list.
First, you need to use the VLOOKUP function to check if the customers in the February list appear in the January list. To do this, type the formula “=VLOOKUP(search_key, range, index, 0)” into your worksheet. In this formula, the “search_key” is the first customer name in the February list, the “range” is the range of sales that contain your January customers, and the “index” is the position of the column within your range that contains the values you want to return (which is one if you only have one column).
Copy this formula down to check all customers in the February list against the January list. The formula will return an “N/A” error if the customer doesn’t appear in the January list.
To make this formula useful for conditional formatting, you need to return “true” wherever there is an “N/A” error. To achieve this, you can use the “ISNA” function. Nest your VLOOKUP function within the ISNA function, like this: “=ISNA(VLOOKUP(search_key, range, index, 0))”.
Copy this formula down and then double click on the cell containing the formula to copy it. Select all of the February customers, go to the Format tab, and then select Conditional Formatting. Create your rule and apply it to the range of cells you have selected (e.g. E3 to E14). In the Format Rules drop-down, select “Custom Formula is” and paste in your formula using “CTRL+V”. The default formatting is green, but you can change this if you prefer.
Once you have highlighted the new customers in the February list, you can delete the column where you created your formula. This completes your task!
Overall, this method is an efficient way to highlight differences between two lists in Google Sheets. By using conditional formatting, you can make the differences stand out and easily identify new customers.