My lookup table is like this:

9:30:55 AM 440.43
9:30:56 AM 440.22
9:30:57 AM 440.03
9:30:58 AM 440.44
9:30:59 AM 440.06
9:31:00 AM 9999

My lookup value is this:

9:37:58 AM
9:30:55 AM

MY lookup formula is this:

=VLOOKUP(R617;table2;2)

but the value returned is 9999 and 440.43 respectively.

If I change the formula to =VLOOKUP(R617;table2;2;false) it returns #N/A for
all lookups.

Any suggestions are appreciated.

Thank you in advance.

but the value returned is 9999 and 440.43 respectively.

This is what you would expect to be returned - in the second case you
have the value in your table, so 440.43 is returned. In the first case,
the value is not found, but you are not looking for an exact value -
instead, Excel returns the value corresponding to the largest item in
the table which is less than the lookup value, so you get 9999.

What suggestions are you hoping for?

Pete

Thank you.

I am trying to get the exact value and if there is no match, return #N/A.

The problem I have is that when I use the lookup
=VLOOKUP(R617;table2;2;false), even if there is an exact match, the formula
returns #N/A.

Could the formatting be the problem ?

"Pete_UK" wrote:

but the value returned is 9999 and 440.43 respectively.

This is what you would expect to be returned - in the second case you
have the value in your table, so 440.43 is returned. In the first case,
the value is not found, but you are not looking for an exact value -
instead, Excel returns the value corresponding to the largest item in
the table which is less than the lookup value, so you get 9999.

What suggestions are you hoping for?

Pete

Times are stored internally in Excel as fractions of a 24-hour day, and
fractions are notoriously difficult to get an exact match on them due
to rounding errors - your two values of 9:30:55 AM may look the same,
but there may be differences in the 10th or lower decimal places. You
could amend your table to this, by multiplying the time column by
24*60*60 (and thus convert the time to integer seconds):


9:30:55 AM 34255 440.43
9:30:56 AM 34256 440.22
9:30:57 AM 34257 440.03
9:30:58 AM 34258 440.44
9:30:59 AM 34259 440.06
9:31:00 AM 34260 9999

and then amend your formula to:

=VLOOKUP(R617*24*60*60;table2;2;false)

where table2 would now be from B1 to C6 instead of A1:B6 (or wherever).

Hope this helps.

Pete