How to Use VLOOKUP with COUNTIF (3 Ways)

Formula with VLOOKUP and COUNTIF

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

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.

Count the Occurrences Using 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.
  • If you want to learn more about this VLOOKUP function, visit this link
  • After that COUNTIF function will search the ID which is got by the VLOOKUP function in the table range F5:K14.
  • For more information about the COUNTIF function please visit this link

formula with COUNTIF and VLOOKUP functions

Step 2: Now enter any name in the C18 cell

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.

Calculate Percentages Using VLOOKUP and COUNTIF functions

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.
  • MATCH(D17,$B$4:$B$13,0) this part is looking for the matched Names with our entered name. And 0 is used to get an exact match. To learn more about this function visit this link
  • 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.

Formula with IF, INDEX, MATCH, COUNTIF and VLOOKUP functions

Step 2: Enter any name in cell D17 and press Enter 

Now enter any name in cell D17 and press Enter 

Step 3: Now enter another name that has less than 4 percentages

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.

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.

COUNTIF vs VLOOKUP for Determining If a Value Exists

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

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.

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

ExcelDemy
Logo