How to use VLOOKUP with COUNTIF in Excel – 3 easy ways

Here are three easy methods!

Microsoft excel logo on a red background with COUNTIF.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you want to know how to use VLOOKUP with COUNTIF, we’ve got you covered.

In this guide, we will discuss how to combine two essential functions in Excel – VLOOKUP and COUNTIF. VLOOKUP helps find specific data in tables, while COUNTIF counts elements based on conditions. These functions, when used together, offer a powerful way to search and count values under specific conditions in any range.

So without any further ado, let’s go through three simple ways to use VLOOKUP with the COUNTIF function, which will make your Excel data analysis more accessible and effective.

Method 1 – Finding the number of occurrences of a specific event

Consider the data set of employee duty slots. In this example, the weekly duty hours of employees are listed. Now, we will count each employee’s number of duty lots in a week.

An easy-to-use spreadsheet featuring a countdown timer.

Step 1: First, select any cell, let’s say C15 in the above example, and type the name of any employee.

A screenshot of an excel spreadsheet showing a time table with easy ways.

Step 2: Now, select the D15 cell and type:

=COUNTIF(C7:H11, VLOOKUP(C15, C7:H11,1,0))

An easy-to-use spreadsheet screenshot in Excel including VLOOKUP and COUNTIF functionalities.

Step 3: Finally, press ENTER.

It will count the number of duty slots of an employee in a week. In other words, it will count the number of times an employee’s name appears in the table.

An example of a spreadsheet in excel with an easy way to create a countdown timer.

Method 2 – Calculating average percentage

Consider the data set of employees and their daily commissions based on sales. Suppose, we need to find the average percentage of weekly commissions of employees if there are at least 5 percentages of commissions. It means that if any employee has less than 5 percent, then we can simply return N/A. Otherwise, the formula would return an average percentage of commission in a week.

A screenshot of a spreadsheet in Excel featuring VLOOKUP and COUNTIF functions.

Step 1: Enter the name of any employee in C13.

A screenshot of a spreadsheet in excel showcasing easy ways to utilize VLOOKUP and COUNTIF functions.

Step 2: Select the cell D13 and type the following formula:

=IF(COUNTIF(INDEX($C$4:$H$8,MATCH(C13,$B$4:$B$8,0),0),”>0″)<5,NA(),VLOOKUP(C13,$B$4:$I$8,8,0))

An example of a spreadsheet in Excel showcasing easy ways to use VLOOKUP and COUNTIF functions.

Step 3: Finally, press ENTER.

An easy ways to use VLOOKUP in Excel.

Here, the formula has returned the exact percentage. You can assume that it is 28% after rounding off.

Method 3 – Determining the existence of a value

In this part, we will discuss how both functions behave when we want to search for something from the data set. As an example, we have the following data set of product IDs and product names. Some of the products are repeated in the data set.

This is an example of a spreadsheet in excel that demonstrates easy ways to use VLOOKUP.

Step 1: Click on B15 and enter any name.

An easy way to use VLOOKUP in Excel - just take a screenshot of your spreadsheet with COUNTIF formulas included.

Step 2: Select the cell C15 and type the following formula:

=COUNTIF($C$4:$C$12,B15)

An easy ways to use VLOOKUP and COUNTIF functions in an Excel spreadsheet.

Step 3: Finally, press ENTER.

An excel spreadsheet featuring VLOOKUP and easy ways for data retrieval.

We will get the number of times the word banana appears from C3 to C12.

Step 4: Now, enter the word banana in the cell E15.

A screenshot of an excel spreadsheet demonstrating easy ways to use VLOOKUP and COUNTIF functions.

Step 5: Click on the cell F15 and enter the following formula:

=VLOOKUP(E15,$C$4:$C$12,1,0)

An easy-to-use spreadsheet in Excel with a screen shot of a COUNTIF formula implementation.

Step 6: Press Enter.

A screenshot of a spreadsheet in excel featuring easy ways to use COUNTIF and VLOOKUP functions.

The result in F15 will be the same as in E15.

Conclusion

And there you have it. These are three ways through which you can use VLOOKUP with COUNTIF. We explained finding occurrences of a specific event, calculating the average percentage, and determining the existence of a value. Remember, make sure to enter the formulas properly, as they won’t work even if you miss adding a single bracket.

Learn more about Excel and its functions with these helpful guides: