You can use the following syntax in Excel 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 Excel**

Suppose we have one dataset in Excel 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:

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 Excel:

Excel: How to Perform a VLOOKUP with Two Lookup Values

Excel: How to Use VLOOKUP to Return Multiple Columns

Excel: How to Use VLOOKUP to Return All Matches

Hi, do you have an error in your formula?

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

A15 has no value.

Should that be A14?