How to Search for Values in a List Using Excel

It’s a common task for many analysts, and helpful for most people in general, so this post will outline how to search for values in a list using Excel. Let’s say you have a master list in column A. In this example, assume it is a list of current customers who have always paid their bills on time. Now your boss hands you another list, and it’s a list of customers who are late on their current bill. Perhaps they do not want to punish the customers who (to this point) have always paid on time, so it’s your job to find out which of the current ‘late’ customers have historically paid on time. This image below is a simplified version of what you may encounter, but the concept is the same.

Now it’s your job to find out if the late customers (Albert, Missy or Dick) have been good customers in the past. Again, you can easily look at each of these lists to figure it out, but it’s assumed most of the lists people deal with have dozens, hundreds or thousands of entries. Simply put your cursor in the cell to the right of ‘Albert Johnson’ from the ‘Currently Late Customers’ list (cell E3) and use this formula:

=COUNTIF($B$3:$B$13, D3)

The COUNTIF formula is simple. The first part asks you for the range to search through. In this case, you want to look through the master list of A+ Customers, which is B3:B13. The dollar signs ($) signifies that the specified range remain static, even when you drag your formula down. The second part of the formula is the criteria you are looking for within the search range. In our case it’s ‘Albert Johnson’ (D3). When you enter this formula, the end result should be “1” in cell E3. Now drag the formula down, and then you’ll see if Missy and/or Dick are also in the list. This formula should be immensely useful for anybody required to analyze data. Please send me any specific questions or comments.