vlookup 1st column time 2nd column text

[Kathl]

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?

 

[Pete_UK]

Are you sure that column A is also in time format, and not text values
that happen to look like times?

Another problem might be that you have seconds in column A that are
not showing, but these will prevent an exact match.

As your table appears to be in ascending order, you might consider
doing a lookup with the fourth parameter set to TRUE (or omit it),
which will then match on the largest value less than the lookup_value.

Hope this helps.

Pete

 

[MartinW]

Hi Kath,

Are you sure that the time values have been entered the same.
Try formatting all of your time cells to general so that you
can see their underlying values.

If that doesn't help post more detail about what "doesn't work" means.

HTH
Martin

 

[Kathl]

The formatting was the same and I also tried to set all formatting to
general, but the cell including the formula still displayed #N/A.

 

[Pete_UK]

So you are not getting an exact match. Try changing your formula to:

=VLOOKUP(D1, $A$1:$B$11, 2)

Hope this helps.

Pete

 

[Kathl]

Tried that one too before. Really strange, this whole thing...

 

[Tyro]

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]

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?

 

[Pete_UK]

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

 

[Kathl]

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]

As I suggested originally, you probably had some times which included
seconds, but you couldn't see them the way your cells were formatted -
this method ensures that no seconds are considered.

Thanks for feeding back - glad it worked for you.

Pete

 

[lucas scott]

Hi,



I really need help in excel..



I have 2 sheets in excel,



Sheet 1:





1croft, Gary

2Eddings, Gary

3Fasci, Nicholas

2Fleming, Tim

3Heinrichs, Michael S

4 Hong, Robert






All I want to do is to show in sheet 2 (which is blank) the NAMES of those who got 2.



All I got is this formula =vlookup(2,table-array,2,0) which works fine but everytime I drag it vertically it shows names names twice (or more)



Eddings, Gary

Eddings, Gary

Fleming, Tim

Fleming, Tim


(sheet 2)





Is there a formula that can prevent this? Help!

 

[Héctor Miguel]

hi, lucas !

see the section for: Arbitrary Lookups in:
-> http://www.cpearson.com/Excel/TablesAndLookups.aspx

hth,
hector.