You can use the following syntax in Excel to use VLOOKUP with a COUNTIF function:
=COUNTIF(D2:G4,VLOOKUP(A15,A2:B11,2,0))
This particular formula uses VLOOKUP to look up the value in cell A15 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(A15,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
