Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
B
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
1  
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
add comment

2 Answers 2

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
add comment

If you want to sum the numbers, you could use

=SUMIF(A1:A6,"D",B1:B6)

to count them

=COUNTIF(A1:A6,"D",B1:B6)
share|improve this answer
    
COUNTIFS was also very helpful. Thanks! –  Thalecress Oct 25 '10 at 21:16
add comment

Your Answer

 
discard

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.