Vlookup multiple items under one ID

[klh84]

Is there a way to use one function to lookup two values under the same unique
ID.

EX:
Account Value
55 20
55 50

I was hoping to find a way to write a vlookup for this that would pull in
the 20, and then also be able to pull in the 50 as well in the same function.
Is this possible?

 

[Don Guillett]

Use data>filter>autofilter
or look in the help index for MATCH to find the first and last match

 

[Mike H]

Hi,

This ARRAY formula will return all matches if you drag down. When it runs
out of matches it will start producing #NUM errors

So with ACCOUNT in column A and VALUE in Column B and the accout to lookup
in E1 try this

=INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.