Multiple VLookups??

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
Are the dates/times true dates/times, or text/formated that way?
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
Use an extra column to get a cheaper index/match like --
Book1.xls
ABCDEFGHIJ
1DateStartStopID#UserconcatTargetDtTargetTmIDUser
21/7/20048:00AM11:35AM1213Mary379930.3333333333333331/7/20044:50PM1214James
31/7/200411:35AM12:38PM1214Mary379930.482638888888889
41/7/200412:38PM4:30PM1214James379930.526388888888889
51/7/20044:30PM4:50PM1214James379930.6875
61/7/20044:50PM8:00PM1214James379930.701388888888889
71/7/20048:00PM11:59PM1213Joe379930.833333333333333
81/8/200412:00AM4:00AM1213Joe379940
91/8/20048:00AM12:15PM1214Mary379940.333333333333333
101/8/20043:30PM3:40PM1213Joe379940.645833333333333
111/8/20043:40PM4:45PM1214Joe379940.652777777777778
121/8/20045:15PM11:00PM1214James379940.71875
131/8/200411:00PM12:00PM1216James379940.958333333333333
141/9/20046:00AM1:00PM1214Joe379950.25
Sheet3
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top