Lookup a date time within a table 1

[G12Consult]

I need help to look up a specified date and time and return column A

In a cell I have say

1. 11/01/2019 05:30 AM
2. 12/01/2019 17:05 PM
3. 13/01/2019 15:15 PM

In two columns I have
A
09/01/2019 17:00 PM 10/01/2019 16:59 PM
10/01/2019 17:00 PM 11/01/2019 16:59 PM
11/01/2019 17:00 PM 12/01/2019 16:59 PM
12/01/2019 17:00 PM 13/01/2019 16:59 PM
13/01/2019 17:00 PM 14/01/2019 16:59 PM

so the above times should return -
1. 10/01/2019 17:00 PM
2. 12/01/2019 17:00 PM
3. 12/01/2019 17:00 PM

 

[combo]

Assuming that:
- there are proper dates in cells,
- reference table is ordered,
- no gaps in date ranges in reference table, so you can check only first column,
- reference table is in A1:B5, searched date is in A11,
you can search only in col. A and test maximal date from cell B5: [tt]=IF(A11>B$5,NA(),INDEX(A$1:A$5,MATCH(A11,A$1:A$5)))[/tt]

combo

 

[novajones001]

Youu can make use of Excel's INDEX and MATCH functions

The INDEX function's syntax and usage
The Excel INDEX function returns a value in an array based on the row and column numbers you specify. The syntax of the INDEX function is straightforward:

INDEX(array, row_num, [column_num])
Here is a very simple explanation of each parameter:

array - this is a range of cells that you want to return a value from.
row_num - the row number in array from which you want to return a value. If omitted, the column_num is required.
column_num - the column number in array from which you want to return a value. If omitted, row_num is required.
If both row_num and column_num parameters are used, the INDEX function returns the value in the cell at the intersection of the specified row and column.

And here is the simplest example of the INDEX formula:

=INDEX(A1:C10,2,3)

The formula searches in cells A1 through C10 and returns a value of the cell in the 2nd row and the 3rd column, i.e. cell C2.

Very easy, right? However, when working with real data you would hardly ever know which row and column you want, that is why you need the help of the MATCH function.

The MATCH function's syntax and usage
The Excel MATCH function searches for a lookup value in a range of cells, and returns the relative position of that value in the range.

For example, if the range B1:B3 contains the values "New-York", "Paris", "London", then the formula =MATCH("London",B1:B3,0) returns the number 3, because "London" is the third entry in the range.

The syntax of the MATCH function is as follows:

MATCH(lookup_value, lookup_array, [match_type])
lookup_value - this is the number or text you are looking for. This can be a value, a cell reference or logical value.
lookup_array - a range of cells being searched.
match_type - this parameter tells the MATCH function whether you want to return an exact match or the nearest match:
1 or omitted - finds the largest value that is less than or equal to the lookup value. The values in the lookup array must be sorted in ascending order, i.e. from smallest to largest.
0 - finds the first value that is exactly equal to the lookup value. In the INDEX / MATCH combination, you almost always need the exact match, so the third argument of your MATCH function is "0".
-1 - finds the smallest value that is greater than or equal to lookup_value. The values in the lookup array must be sorted in descending order, i.e. from largest to smallest.
For more information about the MATCH function, please see How to use MATCH function in Excel.

At first sight, the usefulness of the MATCH function may seem questionable. Who cares about the position of a value in a range? What we do want to know is the value itself.

Let me remind you that the relative position of the lookup value (i.e. a row or/and column number) is exactly what you need to supply to the row_num or/and column_num argument of the INDEX function. As you remember, the INDEX function can return the value at the juncture of a given row and column, but it cannot determine which exactly row and column you want.

 

[SkipVought]

@novajones001,

Welcome to Tek-Tips. Seems you have quite a grasp of the INDEX() and MATCH() functions. But I’d suggest that you carefully read the question and all responses prior to posting a response.

You stated, “Youu can make use of Excel's INDEX and MATCH functions.”

If you had read combo’s response, you might have noticed that he posted a solution that used the INDEX() and MATCH() functions.
[tt]
=IF(A11>B$5,NA(),INDEX(A$1:A$5,MATCH(A11,A$1:A$5)))
[/tt]
Notice also, that he listed the essential assumptions under which his solution will function.

In addition, notice that G12Consult is a programmer. As such, combo addressed the essentials related to understanding how the lookup table should be constructed and assumed his basic understanding of these lookup functions.

Your detailed explanation of use of the INDEX() and MATCH() functions might fit well in the FAQ section of this forum (as no such detailed explanation of INDEX() & MATCH() has been posted) but doesn’t really relate in this context since a complete solution has been already posted.

https://www.tek-tips.com/faq.cfm?pid=68

BTW, back on January 11, I was ready to post a response, very similar to combo’s and almost did until I read his response carefully and noticed that his response was better and more robust than mine. So kudos to combo!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[G12Consult]

I will add people that combo's response worked.

Thank you