# Excel – How to use VLOOKUP result as COUNTIF criteria

countifexcelexcel-formulavlookupworksheet-function

I want to be able to determine how many times a person's particular ID (e.g. `#123`) has appeared in a separate column (e.g. `column C`) given a name (e.g. `Bob`).

I have:

1. Let column A consist of several names. (`Bob`)
2. Let column B consist of IDs relating to the person's name. (`#123`)
3. Let column C consist of those same IDs, however it may contain duplicates of the IDs (might have three `#123`s)

The IDs are side-by-side with each person's respective name.

How can I use the result of a `VLOOKUP` as the criteria (second argument) of the `COUNTIF` function?

If I use `VLOOKUP` separately it returns the correct value that I'm trying to count. `=VLOOKUP(Bob, column A:column B, 2, FALSE)` will return `#123`

If I use `COUNTIF` separately it returns the correct count value as well. `=COUNTIF(column C, #123`) will return `3`

I've tried `=COUNTIF(column C, VLOOKUP(Bob, column A:column B, 2, FALSE))` but it returns `0`.

I've tried referring the criteria for `COUNTIF` to a separate cell with the `VLOOKUP` formula but it still returns `0`.

So it seems like it stops working when I combine the two together. What might the problem be, and what might be an alternative?

@Steve Martin has a valid point, but assuming `Bob` is text, your formula is correct though the syntax should be:
``````=COUNTIF(C:C,VLOOKUP("Bob",A:B,2,0))
If this is what you are using and it is returning `0` rather than the correct result the most likely reason is that the `#123` in ColumnB is not the same as any `#123` in ColumnC. (In turn, the most likely reason for that is probably one or more trailing spaces - that do not show on inspection of cells in ColumnsB:C).
To check, just copy one of the `#123`s from ColumnC over the top of the first `#123` in ColumnB that is immediately to the right of `Bob`. Should you then see a result greater than `0` you are "on the right track" and may want to apply TRIM to remove surplus spaces.
In short, VLOOKUP attempts to match actual cell content rather than visible cell content and it recognises a difference between `#123` and `#123 ` even though it does not display the two differently.