How to Use VLOOKUP with COUNTIF (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

vlookup with countif

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.

entering formula using vlookup with countif

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.

typing names using vlookup with countif

  • 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.

typing functions in combination using vlookup with countif

Formula Breakdown:
  • 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.

calculating existance of a value using vlookup with countif

  • 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


<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo