How can I use VLOOKUP with COUNTIF in Google Sheets?

By /

VLOOKUP and COUNTIF are powerful functions in Google Sheets that can be used together to efficiently search and count data within a spreadsheet. VLOOKUP allows users to search for a specific value in a column and return a corresponding value from a different column, while COUNTIF counts the number of cells in a range that meet a given criteria. By combining these two functions, users can easily and accurately retrieve and count specific data points, making data analysis and management more efficient. This can be particularly useful for large datasets or when dealing with multiple criteria. Overall, the use of VLOOKUP with COUNTIF in Google Sheets can greatly enhance the functionality and accuracy of data analysis.

Google Sheets: Use VLOOKUP with COUNTIF

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. How can VLOOKUP be used with COUNTIF in Google Sheets?
  2. How can I use the SUBTOTAL function with COUNTIF in Google Sheets?
  3. Google Sheets: Use Case Sensitive COUNTIF
  4. Google Sheets: COUNTIF Greater Than But Less Than Some Number
  5. How to Use COUNTIF Contains in Google Sheets?
  6. How to use Google Sheets: COUNTIF Greater Than Zero
  7. How do you Use COUNTIF with Wildcards in Google Sheets?
  8. How to use COUNTIF with OR in Google Sheets?
  9. How can SUBTOTAL be used in Google Sheets with COUNTIF?
  10. How do I use VLOOKUP in Google Sheets to return the last matching value?