Thanks, that was a grat idea. I just inserted this column with the format
changed to text, put this conversion also in the formula and changed the
range of the array and then it worked.


"Pete_UK" wrote:

You could insert a new column B (temporarily) in your lookup table and
put this formula in B1:

=TEXT(A1,"hh:mm")

Copy this down for as many rows as you have by double-clicking the
fill icon (the small black square in the bottom right corner of the
cursor). With the cells still highlighted, <copy, then Edit | Paste
Special | Values (check) | OK then <Esc, which will fix the values.
You can now copy the values from column B to overwrite the values in
column A, and then delete column B.

You can now change your formula to:

=VLOOKUP(TEXT(D1,"hh:mm"), $A$1:$B$11, 2, FALSE)

although I think your table range will be much bigger than this.

Hope this helps.

Pete

On Feb 28, 8:24 am, Kathl wrote:
Yeah, it returned false. Then i converted it into numbers with all decimal
places and it showed for the lookup value 0.684027777777777 and for the value
in the array 0.684027777777778. Now the problem is, that the lookup value is
smaller than the other value and if you set the last parameter of the vlookup
function to true it just searches for a value that is smaller, than this one.

How could I solve that, if I don't want to check all the cells, which would
go into the thousands, if I take all my tables that have to perform the same
function?



"Tyro" wrote:
If D1 has 16:25, kindly show us the value in D1 and the value in A1 - not
the formatted values, the underlying values in the cell.
Also what does the formula =D1=A1 return? TRUE or FALSE? If FALSE, the
value in D1 does not exactly match the value in A1

Tyro

"Kathl" wrote in message
...
hi,

my problem is that the vlookup function doesn't work using the following
tables-

lookup_value is a time format, like 16:25 (assuming here to be in D1)
table_array looks like this
A B
16:25 CN-CCTV-TV
16:40 DK-TV2-TV
16:40 NO-TV2-TV
17:00 HK-PCCW-TV
17:05 AU-SBS-TV
17:05 RU-NTV+-TV
17:20 JP-WOW-TV
17:25 SE-TV4-TV
17:40 CZ-PRMA-TV
17:40 IE-RTE-TV
22:30 CZ-PRMA-TV

In comparing the times I want to get the information in column B.
I implemented VLOOKUP(D1, $A$1:$B$11, 2, FALSE).

Isn't it possible to compare times? How could I solve it?- Hide quoted text -

- Show quoted text -