up vote 1 down vote favorite
share [g+] share [fb]

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?

link|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
feedback

2 Answers

up vote 2 down vote accepted

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

link|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
feedback
up vote 2 down vote

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)
link|improve this answer
COUNTIFS was also very helpful. Thanks! – Thalecress Oct 25 '10 at 21:16
feedback

Your Answer

 
or
required, but never shown
discard

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