Vlookup

[lefdogg]

I am trying to find two different types of data that come from the same
person. For Example:

Name ID Date In Date Out
Adam 001234 12/12/07 12/11/08
Adam 001234 12/12/08 12/11/09

I need to use the ID number as the lookup value. When I use =Vlookup to find
the date out for Adam I can only get the first one, 12/11/08. I am assuming
it is because they are the same number, but is there any way to use the
second ID number in the column to get the second date, 12/11/09?

Thanks

 

[T. Valko]

Assume your data is in the range A210

You want the reasults to appear starting in cell G2 downwards.

F2 = 001234

If your data is sorted or grouped together by ID number:

Entered in G2 and copied down until you get blanks:

=IF(ROWS(G$2:G2)<=COUNTIF(B$2:B$10,F$2),INDEX(D$2$10,MATCH(F$2,B$2:B$10,0)+ROWS(G$2:G2)-1),"")

If your data is in random order then this array formula** :

=IF(ROWS(G$2:G2)<=COUNTIF(B$2:B$10,F$2),SMALL(IF(B$2:B$10=F$2,D$2$10),ROWS(G$2:G2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

 

[Bob Umlas, Excel MVP]

There's an easier way.
Put the ID in F2, then in G2 enter this:
=MATCH($F$2,OFFSET($B$1,G1,0,1000,1),0)+G1
and fill down. This will give you the ROW#s of where the ID appears. So in
H2, you can simply use
=INDEX(D,G2) and fill down
Format G as date, hide F
You can also hide the #N/A you get when you fill down farther than there's
information using conditional formatting.

Bob Umlas
Excel MVP

 

[T. Valko]

There's an easier way.

Easy being relative!