In this article, we will use the VLOOKUP function with the COUNTIF function in Excel. VLOOKUP and COUNTIF are mostly 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.
Download the Practice Workbook
You can download the practice workbook here.
3 Ways to Use VLOOKUP with COUNTIF Function
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 just assume just a weekly attendance. Now we will count each students’ 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 in this case.
- 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 VLOOKUP for Multiple Columns in Excel (6 Examples)
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Excel LOOKUP vs VLOOKUP: With 3 Examples
- How to Use VLOOKUP in VBA in Excel (4 Ways)
- Why VLOOKUP Returns #N/A When Match Exists (with Solutions)
- How to VLOOKUP and Return Multiple Values Vertically 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’s means if any student has less than 4 percentage 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 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 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 (9 Practical Examples)
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 ID. 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 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: VLOOKUP with Multiple Criteria in Excel (6 Examples)
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, then please feel free to share it with us.
Further Readings
- How to Use Named Range in Excel VLOOKUP Function
- VLOOKUP Example Between Two Sheets in Excel
- How to Use VLOOKUP to Search Text in Excel (4 Ideal Examples)
- VLOOKUP with Two Lookup Values in Excel (3 Simple Methods)
- How to Use VLOOKUP for Rows in Excel (With Alternatives)
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use VLOOKUP Function with Exact Match in Excel
