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

vlookup with countif


  • Firstly, select the C16 cell and type any name in the cell.

  • Secondly, choose the C17 cell and type,
  • 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 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

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.


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


  • To start with, click on the E5 cell and any name.

  • Then, select the F5 cell and enter the following formula,
  • 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 I5 cell and enter,
  • 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)


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

Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF