Multiple VLookups??

[poleary2000]

Hi all.

I have the following table:

Date Start Time Stop Time ID # User
1/7/2004 8:00 AM 11:35 AM 1213 Mary
1/7/2004 11:35 AM 12:38 PM 1214 Mary
1/7/2004 12:38 PM 4:30 PM 1214 James
1/7/2004 4:30 PM 4:50 PM 1214 James
1/7/2004 4:50 PM 8:00 PM 1211 James
1/7/2004 8:00 PM 11:59 PM 1213 Joe
1/8/2004 12:00 AM 4:00 AM 1213 Joe
1/8/2004 8:00 AM 12:15 PM 1214 Mary
1/8/2004 3:30 PM 3:40 PM 1213 Joe
1/8/2004 3:40 PM 4:45 PM 1214 Joe
1/8/2004 5:15 PM 11:00 PM 1214 James
1/8/2004 11:00 PM 12:00 PM 1216 James
1/9/2004 6:00 AM 1:00 PM 1214 Joe

Then, I have 5000 lines of transactions that have many, many columns. Two of those columns are Date and Time. What I want to do is to lookup the Date and Time of the transaction in the table above and return the ID # to a cell and the User to another cell. I have fooled around with Lookups and Match but I can't figure it out.

Help!

 

[poleary2000]

I failed to mention that the table of information will grow. Meaning, more dates and more times.

Please help as soon as you can.

Thanks!
Patrick

 

[just_jon]

Is each combination of date and time unique? That is, would you expect to only ever fetch 1 set of values, or values associated with more than 1 matching record.

 

[poleary2000]

Yes...unique. Only one return.

Patrick

 

[BuddieB]

I would add a colum at the beginning that will be your reference column in that column add the DATE and START TIME together (simple addition shoudl work)

Then do the same thing under the transactions.

Once you have this, you should be able to match the start time to the time of the transaction.

if the time of the transaction is between the start and stop time, then you may have to play with the false portion of the VLOOKUP.

Function would look like:

=vlookup(A25, A2:F15, 4, false) for the ID
=vlookup(A25, A2:F15, 5, false) for the USER

You will have to play with the ranges to match, in that case you might want to put these values on separte sheets, as it will be easier to add information to them later on, then you can enlarge your search range to accomodate future entries.

 

[just_jon]

Are the dates/times true dates/times, or text/formated that way?

 

[poleary2000]

They are formatted as true dates and times. Meaning that if I went back and formatted them as numbers, they give the true UNIX decimal time.

Thanks for your help so far.

Patrick

 

[just_jon]

Use an extra column to get a cheaper index/match like --

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1	Date	Start	Stop	ID#	User	concat	TargetDt	TargetTm	ID	User
2	1/7/2004	8:00AM	11:35AM	1213	Mary	379930.333333333333333	1/7/2004	4:50PM	1214	James
3	1/7/2004	11:35AM	12:38PM	1214	Mary	379930.482638888888889
4	1/7/2004	12:38PM	4:30PM	1214	James	379930.526388888888889
5	1/7/2004	4:30PM	4:50PM	1214	James	379930.6875
6	1/7/2004	4:50PM	8:00PM	1214	James	379930.701388888888889
7	1/7/2004	8:00PM	11:59PM	1213	Joe	379930.833333333333333
8	1/8/2004	12:00AM	4:00AM	1213	Joe	379940
9	1/8/2004	8:00AM	12:15PM	1214	Mary	379940.333333333333333
10	1/8/2004	3:30PM	3:40PM	1213	Joe	379940.645833333333333
11	1/8/2004	3:40PM	4:45PM	1214	Joe	379940.652777777777778
12	1/8/2004	5:15PM	11:00PM	1214	James	379940.71875
13	1/8/2004	11:00PM	12:00PM	1216	James	379940.958333333333333
14	1/9/2004	6:00AM	1:00PM	1214	Joe	379950.25
Sheet3

 

[poleary2000]

I am trying it out and can't get it. I'll keep at it.

I may have mispoken also. The TargetDate and TargetTime will not match the table exactly. They may be within the range of the Start and Stop Times. Would I have to modify things then?

 

[poleary2000]

Sorry. Mistake on my part. I think it is working.