How can VLOOKUP be used with COUNTIF in Google Sheets?

By /

VLOOKUP and COUNTIF can be used together in Google Sheets to search for specific criteria and count the number of occurrences that match that criteria. VLOOKUP can be used to retrieve data from a table based on a search key, while COUNTIF counts the number of cells within a range that meet a certain condition. By using VLOOKUP within the COUNTIF function, we can search for a specific value and count how many times it appears in a range of data. This allows for efficient and accurate data analysis and organization in Google Sheets.

You can use the following syntax in Google Sheets to use VLOOKUP with a COUNTIF function:

=COUNTIF(D2:G4,VLOOKUP(A14,A2:B11,2,0))

This particular formula uses VLOOKUP to look up the value in cell A14 in the range A2:B11 and returns the corresponding value in the second column.

Then, the formula uses COUNTIF to count the number of times the value returned by the VLOOKUP function occurs in the range D2:G4.

The following example shows how to use this formula in practice.

Example: How to Use VLOOKUP with COUNTIF in Google Sheets

Suppose we have one dataset in Google Sheets that contains basketball team names and team ID values.

Suppose we also have a dataset that contains the three team ID values that scored the most points in various weeks:

Suppose we would like to count the number of times that the team ID value associated with the Lakers occurred in the dataset that shows the highest-scoring teams per week.

We can type the following formula into cell B14 to do so:

=COUNTIF(D2:G4,VLOOKUP(A14,A2:B11,2,0))

The following screenshot shows how to use this formula in practice:

Google Sheets VLOOKUP with COUNTIF

This formula uses a VLOOKUP to find “Lakers” in the team column and return a value of 405.

It then uses COUNTIF to count the number of times 405 occurred in the weekly dataset and returned a value of 3.

We can verify that this is correct by manually identifying the three times that the value 405 occurred in the weekly dataset:

We can see that the value 405 does indeed occur 3 times.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. Google Sheets: Use Case Sensitive COUNTIF
  2. Google Sheets: COUNTIF Greater Than But Less Than Some Number
  3. How to Use COUNTIF Contains in Google Sheets?
  4. How to use Google Sheets: COUNTIF Greater Than Zero
  5. How do you Use COUNTIF with Wildcards in Google Sheets?
  6. How to use COUNTIF with OR in Google Sheets?
  7. How can SUBTOTAL be used in Google Sheets with COUNTIF?
  8. How to Use IFERROR with VLOOKUP in Google Sheets
  9. How to Use a Case Sensitive VLOOKUP in Google Sheets
  10. How can VLOOKUP be used with CONCATENATE in Google Sheets?