Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

VLOOKUP almost does what I need, but it won’t retrieve all the numbers associated with a list value that’s in the table multiple times. E.g. for table,
A 1
A 2
C 1
D 1
D 2

Is there an alternative function to return 1 and 2 for lookup value D? If I had to use a macro, what would it look like?

share|improve this question
What format would multiple values be returned in? – Lance Roberts Oct 21 '10 at 22:03
How many times the value to look for can appears in your list? – Toc Oct 22 '10 at 20:29
47 out of 939K rows. – Thalecress Oct 25 '10 at 21:15
up vote 2 down vote accepted

Have you tried using a filter fed by a named range?

share|improve this answer
This worked! I had ~1M list rows, with 40 lookup values of interest, yielding 46 associated numbers. – Thalecress Oct 25 '10 at 21:00

If you want to sum the numbers, you could use


to count them

share|improve this answer
COUNTIFS was also very helpful. Thanks! – Thalecress Oct 25 '10 at 21:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.