We often need to get the result of a particular task in the fastest possible way. Like, if an employee of a company has a dataset containing records of all the workers like their attendance, average working hours, and much more, and if he wants to see all the records of a particular worker, he doesn’t have time to walk through the whole dataset and find them one by one. So what can he do? He can combine some amazing formulas like** IF, INDEX, MATCH, VLOOKUP, COUNTIF** and much more to get his desired result within the shortest possible time. In this article, we have demonstrated some examples on how to use **VLOOKUP** with **COUNTIF** function in Google Sheets.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download the spreadsheet used to demonstrate examples in this article.

**4 Ideal Examples to Use VLOOKUP with COUNTIF Function in Google Sheets**

We can use the **VLOOKUP **and **COUNTIF** functions in many ways. Here we have described 4 usual examples that are necessary from many perspectives.

**Example 1. Finding Total Appearances of Attendees in a Session**

Let’s assume, the following dataset is containing the records of 6 attendees’ appearances of a 5-day session on a particular topic. We want to calculate the total appearances of each attendee in that session using **VLOOKUP **and **COUNTIF** functions.

__Steps:__

- First, select
**Cell B15**and then apply the following formula below and press**Enter**–

`=VLOOKUP(B5,$D$6:$H$11,1,0)&"-"&COUNTIF($D$6:$H$11,B5)`

- Here, we can see in
**Cell B15**that the total number of appearances of attendee “Annie” is 4.

__Formula Breakdown__

**COUNTIF($D$6:$H$11,B5)**

Counts the total number of presence that is given input in **Cell B5** within **Cell range D6:H11**.

**VLOOKUP(B5,$D$6:$H$11,1,0)**

Looks for value in **Cell B5** within **Cell range D6:H11** and returns the corresponding value from **Column D** if the lookup value exists within that range.

**VLOOKUP(B5,$D$6:$H$11,1,0)&”-“&COUNTIF($D$6:$H$11,B5)**

Finally, the **&** operator will join the outputs with a hyphen.

- Now if I input “Annie” in a different cell within
**Cell range D6:H11**, the total number of appearances will be changed. Like, if I input “Annie” in**Cell F8**then see what happens!

- The total number of appearances of “Annie” is now 5.
- Now, simply drag down using the
**Fill Handle**icon as shown in the circled portion.

- And you will get the total number of appearances of the other attendees as well.

**Read More: ****Google Sheets Count Cells from Another Workbook with COUNTIF Function**

**Example 2. Counting Total Attendance**

Suppose, in the following dataset, one-week attendance record of some students is given along with their ID number. What we want to know is the total attendance of a particular student within just a moment.

__Steps:__

- In the following dataset, select
**Cell H18**and apply the formula given below then press**Enter**–

`=COUNTIF(E5:J14,VLOOKUP(H17,B5:C14,2,0))`

- Here we can see what appears in
**Cell H18**is “**0**”. It’s because nothing is given in**Cell H17**as input.

__Formula Breakdown__

**VLOOKUP(H17,B5:C14,2,0)**

After giving input in **Cell H17** the **VLOOKUP** function will look for that value within **Cell range B5:C14**. Then it will match the corresponding number for the input in **Cell H17**. Like if we input “Robert” then the function will match with its corresponding number that is “9”.

**COUNTIF(E5:J14,VLOOKUP(H17,B5:C14,2,0))**

The number produced by the **VLOOKUP** function within the** Cell range E5:J14** is counted using the **COUNTIF** function.

- Now, if you type down one of the students’ names in
**Cell H17,**the total number of attendances of that student will appear in**Cell H18**. Like, if you type down “Annie” in**Cell H17**the total number of attendances of “Annie” will appear in**Cell H18**and that will be “4”.

**Read More: ****[Fixed!] COUNTIF Function Is Not Working in Google Sheets**

**Similar Readings**

**How to Execute Case Sensitive COUNTIF in Google Sheets****Google Sheets Add Calculated Field for Pivot Table with COUNTIF****How to Use COUNTIF Function with OR Logic in Google Sheets**

**Example 3. Confirming Match and Calculating Total Count**

Presume, we have a dataset of some students with their ID. What we want to check is whether a student is on the dataset list or not and if he exists then what count of that particular student is present in the dataset?

__Steps:__

- In the following dataset, select
**Cell F5**then apply the following formula-

`=VLOOKUP(E5,$B$5:B,1,0)`

- As nothing is typed down yet in
**Cell E5,**that’s why the match is shown as “**#N/A**”. - Now apply the following formula in
**Cell G5**and then press**Enter**–

`=COUNTIF($B$5:B,E5)`

- The result is showing “
**0**” as no input is given in**Cell E5**. - Now type down any name from the list or outside the list in
**Cell E5**and it will show satisfactory results. Like if you type down “Annie” in**Cell E5**then it will show “Annie” in**Cell F5**and the total number of counts for “Annie” will be shown “4” in**Cell F5**as the name “Annie” is on the list about 4 times.

- And if we input a random name that is not on the list, then the result will be as follows-

**Read More: ****COUNTIF Function with “Not Equal to” Criterion in Google Sheets**

**Example 4. Calculating Percentage**

Let’s assume, we have a dataset of grade sheets of some students. Now, if there are at least 3 percentages for each grade, our target is to get their average percentage. That means if any student has more or equal to 3 percentages, the result will be the average percentage. If any student has less than 3 percentages, then the result will return as “**#N/A**”. We will be using the combination of** VLOOKUP**, **COUNTIF**, **IF**, **INDEX**, **MATCH** and **NA** functions.

__Steps:__

- First, select
**Cell C18**, apply the following formula and press**Enter**–

`=IF(COUNTIF(INDEX($C$5:$G$14,MATCH(C17,$B$5:$B$14,0),0),">0")<3,NA(),VLOOKUP(C17,$B$5:$H$14,7,0))`

__Formula Breakdown__

**VLOOKUP(C17,$B$5:$H$14,7,0)**

Looks for the value in **Cell C17** within **Cell range B5:H14** and return the corresponding value for **Cell C17** from column 7. And if false then it will return “0”.

**NA()**

Returns an error as “**#N/A**” if the value is not available.

**MATCH(C17,$B$5:$B$14,0)**

Will return the corresponding value of **Cell C17 **that is within **Cell range B5:B14**.

**INDEX($C$5:$G$14,MATCH(C17,$B$5:$B$14,0),0)**

This formula works as **INDEX($C$:$B$5,5,0)** which is basically the set of percentages corresponding to **Cell C17**.

**COUNTIF(INDEX($C$5:$G$14,MATCH(C17,$B$5:$B$14,0),0),”>0″)**

This counts the occurrences of percentages for the lookup value.

**IF(COUNTIF(INDEX($C$5:$G$14,MATCH(C17,$B$5:$B$14,0),0),”>0″)<3,NA(),VLOOKUP(C17,$B$5:$H$14,7,0))**

Returns back the typical proportion for **Cell C17** in Column 8 for the condition “**<3**”.

- What we can see is the result returned as “
**#N/A**”. This is because no such entry is given in**Cell C17**. - Now, suppose we want to know the average percentage result of a student named “Maria”. Simply type down “Maria” in
**Cell C17**and press**Enter-**

- We can see that the average percentage mark of “Maria” is “63%”. The result returned as an average percentage form as “Maria” has 4 percentages of 4 subjects which is obviously greater than 3.
- Now, we will type down “Eva” in
**Cell C17,**and let’s see what happens-

- The result returned as “
**#N/A**” because “Eva” has only percentages of 2 subjects which is less than 3.

**Read More: ****COUNTIF with Greater than and Less than Criteria in Google Sheets**

**Conclusion**

We can solve almost all the time-consuming tasks within the shortest possible time in Google Sheets or Excel. Using **VLOOKUP** and **COUNTIF** is just a small part of that. In this article, we have described 4 ideal examples on how you can use **VLOOKUP** with **COUNTIF** in Google Sheets. Hope this will help you with your task. Visit our site **officewheel.com** to see more related articles.