Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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 On Feb 27, 1:45*pm, Kathl wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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" 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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. "MartinW" wrote: 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" 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
So you are not getting an exact match. Try changing your formula to:
=VLOOKUP(D1, $A$1:$B$11, 2) Hope this helps. Pete On Feb 27, 2:44*pm, Kathl wrote: 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. "MartinW" wrote: 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" 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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
Tried that one too before. Really strange, this whole thing...
"Pete_UK" wrote: So you are not getting an exact match. Try changing your formula to: =VLOOKUP(D1, $A$1:$B$11, 2) Hope this helps. Pete On Feb 27, 2:44 pm, Kathl wrote: 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. "MartinW" wrote: 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" 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 - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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 - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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 - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup 1st column time 2nd column text
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 On Feb 28, 9:49*am, Kathl wrote: 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 -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula-help!
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! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup formula-help!
hi, lucas !
see the section for: Arbitrary Lookups in: - http://www.cpearson.com/Excel/TablesAndLookups.aspx hth, hector. 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Text to Columns - Way to remove all column breaks at one time? | Excel Discussion (Misc queries) | |||
Template to convert column of text to time | Excel Worksheet Functions | |||
Pivot Table (vlookup 2 column text values, return 1 value) | Excel Discussion (Misc queries) |