Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |