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.
Step 1: First, select any cell, let’s say C15 in the above example, and type the name of any employee.
Step 2: Now, select the D15 cell and type:
=COUNTIF(C7:H11, VLOOKUP(C15, C7:H11,1,0))
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.
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.
Step 1: Enter the name of any employee in C13.
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))
Step 3: Finally, press ENTER.
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.
Step 1: Click on B15 and enter any name.
Step 2: Select the cell C15 and type the following formula:
=COUNTIF($C$4:$C$12,B15)
Step 3: Finally, press ENTER.
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.
Step 5: Click on the cell F15 and enter the following formula:
=VLOOKUP(E15,$C$4:$C$12,1,0)
Step 6: Press Enter.
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:
