Vlookup
 

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

Vlookup
 

Assume your data is in the range A2:D10

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: D$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:D$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

--
Biff
Microsoft Excel MVP


"lefdogg" wrote in message
...
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

Vlookup
 

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: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" wrote:

Assume your data is in the range A2:D10

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: D$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:D$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

--
Biff
Microsoft Excel MVP


"lefdogg" wrote in message
...
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

Vlookup
 

There's an easier way.

Easy being relative!

--
Biff
Microsoft Excel MVP


"Bob Umlas, Excel MVP" wrote in
message ...
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: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" wrote:

Assume your data is in the range A2:D10

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: D$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:D$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

--
Biff
Microsoft Excel MVP


"lefdogg" wrote in message
...
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