How to use the COUNTIF Function Instead of VLOOKUP + Video
Bottom line: Learn how to use the COUNTIF function instead of VLOOKUP to determine if a value exists in a list or range.
Skill level: Beginner
Problem – I want to check and see if the names in column B exist in this long list of names in column E.
There are a lot of different ways we can solve this problem with formulas in Excel. You might be inclined to use a VLOOKUP formula, and that would work just fine.
However, there are a few advantages to using the COUNTIF function instead.
- It’s faster
- And less prone to errors.
So let’s learn how COUNTIF works.
Video Tutorial on How to Use COUNTIF
Here is a video from The Ultimate Lookup Formulas Course that explains how to use COUNTIF instead of VLOOKUP.
*Double-click the video to watch full screen.
There are over 100 short videos just like the one above included in the Ultimate Lookup Formulas Course.
This course has been designed to help you master Excel’s most important functions and formulas in an easy step-by-step manner.
Download the Example File
Download the Excel file to follow along.
The COUNTIF Function Explained
COUNTIF is a very simple function. It’s job is to search a range for a value and tell you how many cells match that value.
In the example below I am using a COUNTIF function to return the number of times the word “Surf” exists in the range. The result is 3.
Arguments: COUNTIF only has two arguments (Range, Criteria).
- Range – this is the range we want to search for the lookup value (Criteria).
- Criteria – this is the value we want to find.
Rules: By default COUNTIF is going to look for an exact match of each cell’s entire value. You can add wild cards and conditional/relational operators to the criteria to look for partial matches.
Result: COUNTIF returns a numerical value for the number of cells that matched the criteria in the range. If the COUNTIF returns a number greater than or equal to 1, then that means the value exists in the list. If COUNTIF returns a zero then there are no cells that match the criteria.
So back to our original problem, we can use COUNTIF to see how many times the names exist in the list.
The cells in C2:C4 contain the COUNTIF function.
When COUNTIF returns a zero (0) this means the value does not exist. So the result in cell C4 tells us that the name Bob Stevens does not exist in the list.
Why Use COUNTIF Instead of VLOOKUP?
When you just want to determine if a value exists in a list then I recommend using COUNTIF over VLOOKUP. It has a few advantages that make it more efficient, and also give you more insight to your data.
Here are 3 reasons to use COUNTIF instead of VLOOKUP (when you just want to see if a value exists in a range of cells):
- The COUNTIF function only has two arguments making it really fast and easy to write the formula. VLOOKUP has four arguments.
- COUNTIF returns the total number of matching values in the range, so you can see if there is more than one matching value. VLOOKUP cannot do this, it only returns the first match.
- If the value does not exist, COUNTIF will return a zero (0). You do not need to worry about a formula error. With VLOOKUP, the formula would return an error and you would use and error handling function like IFERROR to handle the error.
Here is a comparison table of the list above, just in case your boss asks why… 🙂
Checkout my article on VLOOKUP Explained at Starbucks if you want to learn more about how the function works.
COUNTIF vs VLOOKUP for Determining If A Value Exists
Here is an example of solving the same problem with COUNTIF vs VLOOKUP.
The COUNTIF returns a number greater than or equal to 1 if the value exists in the list. It returns a zero if the value does not exist.
The VLOOKUP formula is going to return the matching value from the list. VLOOKUP returns a #N/A error if it can’t find the value in the list.
Obviously the VLOOKUP formula is longer, more complex, and will take more time to write.
Don’t Forget About COUNTIFS
The COUNTIF function has a big brother named COUNTIFS. This function allows you to specify multiple criteria in multiple ranges. You could use it to answer the question of how many Bob’s are in the East region from the data set below.
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)
You can add up to 127 criteria!
You can also use COUNTIFS with only one criteria, just like you do with COUNTIF.
That means you could use COUNTIFS all the time instead of COUNTIF. I know a lot of people do this and that is perfectly fine. I wanted to keep it simple by explaining COUNTIF first.
So now you know two new functions that you can use instead of VLOOKUP to find a matching value!
Please leave a comment below with any questions.