Vlookup

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 26, 2008

• #2

=LOOKUP(StartTime,StartTimeRange,OrderNumRange)

 

Upvote 0

D

dcyuen

Board Regular

Joined

Jun 2, 2008

Messages

56

• Nov 27, 2008

• #3

Thanks. Hey i remember you helped me last time! Sorry I do not exactly understand. Let say if I have a massive order# in File A, how could I Lookup the first Start-Time and the last End-Time of Order#,12345?
Thanks a lot!

 

Upvote 0

gardnertoo

Well-known Member

Joined

Jul 24, 2007

Messages

938

• Nov 27, 2008

• #4

Here's one way:

Book1
	A	B	C	D	E	F	G
1	Order#	Start time	End time			Earliest Start	Latest End
2	12345	9:00	10:00		12345	9:00	11:00
3	12345	10:00	11:00		23456	11:00	13:00
4	23456	11:00	13:00
5	34567	13:00	15:00
Sheet1

Instead of VLOOKUP, I've used the combination of INDEX and MATCH. The formula in column F accomplishes what Aladin's VLOOKUP suggestion would have. The formula in column G is a little different. When searching through a list of numbers, if the MATCH function finds an entry equal to the number for which it is searching, the function returns the row number of the first such entry. Otherwise it returns the row number of the last entry less than the number for which it is loooking. To take advantage of this behaviour, the formula in column G is looking for a number slightly bigger than the order number. It won't find 12345.1 in the list, so instead it will return the row number of the last time it finds 12345 in the list.

 

Upvote 0

D

dcyuen

Board Regular

Joined

Jun 2, 2008

Messages

56

• Nov 27, 2008

• #5

Thank you very much and your fast reply! I am working in Hong Kong right now so is day time over here. But I do used to live in Arizona before.
Everything is working and I get the concept, but one thing I don't unstand is when there are 2 numbers that are the same, why INDEX only address the the first one?

 

Upvote 0

gardnertoo

Well-known Member

Joined

Jul 24, 2007

Messages

938

• Nov 27, 2008

• #6

...when there are 2 numbers that are the same, why INDEX only address the the first one?

Click to expand...

The reason is that the MATCH and LOOKUP functions don't have a way for you to tell it "There will be more than one valid result, show me the nth one". (Unlike, for example, the SUBSTITUTE function, where you can replace the nth instance of your search string.) So, we have to set up the search criteria in a way that forces the single result to be the one we want. Also, I forgot to mention that your data has to be sorted by order number for my formula to work.

 

Upvote 0

D

dcyuen

Board Regular

Joined

Jun 2, 2008

Messages

56

• Nov 28, 2008

• #7

Oh, the order# is entered by the operator and is not in sorted order.

Any other solution to solve the END-Time lookup? I still having trouble lookup the END-TIME with the formula, it results random END-TIME for some reason -_- Please advice and thanks!

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Nov 28, 2008

• #8

You want the last end time from the table, i.e. the one associated with the last instance of that specific order number in the table (which isn't necessarily the latest time)?

Try

=LOOKUP(2,1/(order_number_range=order_number),end_time_range)

 

Upvote 0

D

dcyuen

Board Regular

Joined

Jun 2, 2008

Messages

56

• Nov 28, 2008

• #9

Thanks for your reply.

Error: #NUM! (cannot enter as A:A, must be a given range)

Nevermind, it's working! that's amazing, how that happen? Thansk!

 

Last edited: Nov 28, 2008

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 29, 2008

• #10

dcyuen said:

Thanks for your reply.

Error: #NUM! (cannot enter as A:A, must be a given range)

Nevermind, it's working! that's amazing, how that happen? Thansk!

Click to expand...

Would you please state the lookup value (or values if more than one) and the expected result with respect to the initial sample you posted?

 

Upvote 0