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.

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

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

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

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