In this article, we will use the VLOOKUP function with the COUNTIF function in Excel. VLOOKUP and COUNTIF are the most used and powerful functions in MS Excel. VLOOKUP is used to find any specific data from any table and the COUNTIF function is for counting elements using conditions. With the combined formula of these two functions, we can search and count any values with conditions from any range. In this article, I will show various ways to use VLOOKUP with the COUNTIF function.
Use VLOOKUP with COUNTIF Function: 3 Ways
In this article, we will talk about the usage of VLOOKUP with COUNTIF in 3 ways. Firstly, we will use the combination to find out the occurrences of a particular event. Secondly, we will calculate the average percentage of a certain set of percentages. Finally, we will find out the existence of a value by using VLOOKUP with COUNTIF.
1. Count the Occurrences Using VLOOKUP and COUNTIF Functions
Let’s consider a dataset of student attendance. For this example, we assume just a weekly attendance. Now we will count each student’s total attendance using the VLOOKUP and COUNTIF functions.
Steps:
- Firstly, select the C16 cell and type any name in the cell.
- Secondly, choose the C17 cell and type,
=COUNTIF(F5:K14,VLOOKUP(C16,B5:C14,2,0))- Finally, hit Enter.
- As a result, we will find the number of attendances for the student.
Formula Breakdown:
- VLOOKUP(C16,B5:C14,2,0): The VLOOKUP function will match the value in the C16, lookup value, in the look up range B5:C14. Then, it matches the number associated with the name in the C16 cell in the second column of the range which is 13.
- COUNTIF(F5:K14,VLOOKUP(C16,B5:C14,2,0)): The COUNTIF function counts the number returned by the VLOOKUP(C16,B5:C14,2,0) expression which is 13 in the F5:K14 range and returns the number of appearance for the number 13. In this case, it will be 5.
Read More: How to Use IF ISNA Function with VLOOKUP in Excel
2. Calculate Percentages Using VLOOKUP and COUNTIF functions
Let’s have a dataset of student marks for each course (like 6 courses). Now our concern is to find the average percentages of all the grades if there are at least 4 percentages of the grades. That means if any student has less than 4 percent then we will simply return #NA! Otherwise, we will return the average percentages of the grades.
Steps:
- To begin with, choose the C16 cell and enter any name in the cell.
- Then, select the C17 cell and enter the following formula,
=IF(COUNTIF(INDEX($C$5:$H$14,MATCH(C16,$B$5:$B$14,0),0),">0")<4,NA(),VLOOKUP(C16,$B$5:$I$14,8,0))- Press Enter.
- As a result, we will get the average percentage of the students.
- VLOOKUP(C16,$B$5:$I$14,8,0): returns the [value_if_false] argument of the IF function. It is basically the average percentage of the obtained marks by Daniel.
- Output: 41%
- NA(): will return an error if the logical test argument of the IF function becomes TRUE. Here, Daniel attended more than 4 courses which is not the desired condition, so this part will return an error.
- Output: #N/A
- MATCH(C16,$B$5:$B$14,0): it will return the relative position of Daniel in the cell range B5:B14.
- Output: 6
- INDEX($C$5:$H$14,MATCH(C16,$B$5:$B$14,0),0) —-> simplifies to
- INDEX($C$5:$H$14,6),0): returns the set of percentages for Daniel.
- Output: {0.25,0.6,0.25,0.25,0.6,0.5}
- COUNTIF(INDEX($C$5:$H$14,MATCH(C16,$B$5:$B$14,0),0),”>0″) —-> turns into
- COUNTIF({0.25,0.6,0.25,0.25,0.6,0.5},”>0″): counts the percentages if the value is greater than 0.
- Output: 6
- So the whole formula simplifies to
- IF(6<4, #N/A, 41%): returns the average percentage of Daniel as 6<4 is not a true condition.
- Output: 41%.
Â
Read More: INDEX MATCH vs VLOOKUP FunctionÂ
3. COUNTIF vs VLOOKUP for Determining If a Value Exists
In this section, we will try to find out how COUNTIF and VLOOKUP functions handle searching operations. To be specific we will see if the total count of any element is zero then what will be returned by COUNTIF and VLOOKUP functions. Let’s assume we have a dataset of employees with their names and IDs. There are repeated values in the table. Now, we will count the names and try to match them.
Steps:
- To start with, click on the E5 cell and any name.
- Then, select the F5 cell and enter the following formula,
=COUNTIF($C$5:$C$14,E5)- Press Enter after that.
- As a result, we will get the number of times the name appeared in the range C5:C14.
- After that, choose the H5 cell and any of the names.
- Thereafter, choose the I5 cell and enter,
=VLOOKUP(H5,$C$5:$C$14,1,0)- Consequently, we will get the same name as in the H5 cell in the I5 cell.
Read More: Excel LOOKUP vs VLOOKUP
Download the Practice Workbook
You can download the practice workbook here.
Conclusion
These are some ways to use the VLOOKUP function with COUNTIF in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, please share it with us.
Further Readings
- Combining SUMPRODUCT and VLOOKUP Functions in Excel
- How to Combine SUMIF and VLOOKUP in ExcelÂ
- Use VLOOKUP to Sum Multiple Rows in Excel
- XLOOKUP vs VLOOKUP in Excel
- How to Use Nested VLOOKUP in Excel
- IF and VLOOKUP Nested Function in ExcelÂ
- How to VLOOKUP and SUM Across Multiple Sheets in Excel
- How to Use VLOOKUP Function with INDIRECT Function in Excel
- How to Use IFERROR with VLOOKUP in Excel
- VLOOKUP with IF Condition in Excel
<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
