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.

**Table of Contents**hide

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

**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%**

- Output:
**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**

- Output:
**MATCH(C16,$B$5:$B$14,0):**it will return the relative position of**Daniel**in the cell range**B5:B14.**- Output:
**6**

- Output:
**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}**

- Output:
**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**

- Output:
- 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%**.

- Output:

** **

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