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.
- Let column A consist of several names. (
- Let column B consist of IDs relating to the person's name. (
- Let column C consist of those same IDs, however it may contain duplicates of the IDs (might have three
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
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
If I use
COUNTIF separately it returns the correct count value as well.
=COUNTIF(column C, #123) will return
=COUNTIF(column C, VLOOKUP(Bob, column A:column B, 2, FALSE)) but it returns
I've tried referring the criteria for
COUNTIF to a separate cell with the
VLOOKUP formula but it still returns
So it seems like it stops working when I combine the two together. What might the problem be, and what might be an alternative?excelexcel-formulavlookupworksheet-functioncountif
In my case:
=COUNTIF(C:C,VLOOKUP(A1,A:B, 2, FALSE))
works perfectly. Always try to recalculate formulas by F9. Gl!
@Steve Martin has a valid point, but assuming
Bob is text, your formula is correct though the syntax should be:
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 even though it does not display the two differently.