# How to Use VLOOKUP with COUNTIF (3 Ways) 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.

## 3 Ways to Use VLOOKUP with COUNTIF Function

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 student’s total attendances using the VLOOKUP and COUNTIF functions. Step 1: Enter the formula in cell C19 and press Enter

`=COUNTIF(F5:K14,VLOOKUP(C18,B5:C14,2,0))`

Formula Explanation

• First look at the VLOOKUP function VLOOKUP(C18, B5:C14,2,0). C18 in this cell we will put the input data. Then B5:C14 is the range where we will search the data. 2 is for two columns (Name and ID) and the last 0 is for an exact match.
• After that COUNTIF function will search the ID which is got by the VLOOKUP function in the table range F5:K14. Step 2: Now enter any name in the C18 cell ### 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. Step 1: Enter the formula in cell D18

`=IF(COUNTIF(INDEX(\$C\$4:\$H\$13,MATCH(D17,\$B\$4:\$B\$13,0),0),">0")<4,NA(),VLOOKUP(D17,\$B\$4:\$I\$13,8,0))`

Formula Explanation

• Here along with VLOOKUP and COUNTIF, we are using IF, INDEX, MATCH functions also.
• INDEX(\$C\$4:\$H\$13, MATCH(D17,\$B\$4:\$B\$13,0),0),”>0″)<4 this part is checking if the matched named has no 0 percentages. For more information visit this link
• COUNTIF(INDEX(\$C\$4:\$H\$13, MATCH(D17,\$B\$4:\$B\$13,0),0),”>0″)<4 using this we are counting if not zero cells have less than 4 percentages or not.
• Lastly using the IF function we are checking if the number of percentages is less than 4 or not. If the result is true then print #NA using NA() function. Otherwise, print the average marks with the help of the VLOOKUP function. Step 2: Enter any name in cell D17 and press Enter Step 3: Now enter another name that has less than 4 percentages ### 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.

Read more: VLOOKUP to Search Text in Excel

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. Step 1: Now enter the formula in cells F4 and J4. Copy the formula up to F6 and J6

For F4:

`=COUNTIF(\$C\$4:\$C\$13,E4)`

For J4:

=VLOOKUP(I4,\$C\$4:\$C\$13,2,0)

Step 2: Then enter the names in the input cells and see the output As we can see if there is not match data then COUNTIF simply returns 0 on the other side VLOOKUP returns #NA error if there is no matched data.

## VLOOKUP VS COUNTIF

VLOOKUP COUNTIF
The VLOOKUP function simply returns the search data from any range. COUNTIF returns the total count with only one condition.
Returns #NA if the data is missing in the range. Returns 0 if the data is missing in the range.
VLOOKUP formula is longer and takes more time to write. COUNTIF is shorter than VLOOKUP and easy to use.

## Things to Remember

Common Errors When they show
#N/A in VLOOKUP In practice, there are many reasons why you might see this error, including:
• The lookup value does not exist in the table
• The lookup value is misspelled or contains extra space.
• The table range is not entered correctly.
• You are copying VLOOKUP, and the table reference is not locked.
Case-sensitive COUNTIF is not case-sensitive.
#VALUE in COUNTIF COUNTIF will return a #VALUE error when referencing another workbook that is closed.
Incorrect results of COUNTIF COUNTIF returns incorrect results when used to match strings longer than 255 characters.

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