How to use VLOOKUP result as COUNTIF criteria
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:
- Let column A consist of several names. (
Bob)- Let column B consist of IDs relating to the person's name. (
#123)- Let column C consist of those same IDs, however it may contain duplicates of the IDs (might have three
#123s)
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 #123s 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.