Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
I am doing a match function using dates. Column A has weekly dates with a
Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. -- David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
Hi!
Try this: =MATCH("01/07/05",INDEX(TEXT(A1:A10,"mm/dd/yy"),,1),0) Biff "David" wrote in message ... I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. -- David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
Hi Biff,
This works, I get the correct row as the answer, assuming the date exists in the table. But if I experiment, change the date to a date that does not exist and the last index parameter to -1, i begin to get erroneous answers. =MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields the correct row, 359. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields #N/A, which is expected, if i read the formulas correctly. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),-1) this formula yields 46, which is not what was expected at all. The date changed by one day only and I expected with the -1 parameter change would get the date just below that date (the closest date, but below it.) The date that found is 01/14/05. In the end i should be able to take todays date, subtract 365 days and find the closest date that either matches it exactly or is just a little less than that date. It is very unlikely that it would ever match exactly. -- David "Biff" wrote: Hi! Try this: =MATCH("01/07/05",INDEX(TEXT(A1:A10,"mm/dd/yy"),,1),0) Biff "David" wrote in message ... I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. -- David |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
If Date is in ascending order:
=MATCH("01/11/99"+0,DateRange,1) Otherwise, replace 1 with 0. David wrote: I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
The -1 does not offset the result, it tells excel what match type to use,
you need all number/dates to be sorted in descending order and it will find the smallest value that is greater than or equal to lookup value, if you want to offset the result put -1 after the formula but do not change the match type =MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0)-1 -- Regards, Peo Sjoblom (No private emails please) "David" wrote in message ... Hi Biff, This works, I get the correct row as the answer, assuming the date exists in the table. But if I experiment, change the date to a date that does not exist and the last index parameter to -1, i begin to get erroneous answers. =MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields the correct row, 359. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields #N/A, which is expected, if i read the formulas correctly. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),-1) this formula yields 46, which is not what was expected at all. The date changed by one day only and I expected with the -1 parameter change would get the date just below that date (the closest date, but below it.) The date that found is 01/14/05. In the end i should be able to take todays date, subtract 365 days and find the closest date that either matches it exactly or is just a little less than that date. It is very unlikely that it would ever match exactly. -- David "Biff" wrote: Hi! Try this: =MATCH("01/07/05",INDEX(TEXT(A1:A10,"mm/dd/yy"),,1),0) Biff "David" wrote in message ... I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. -- David |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
I would recommend Aladin's formula. Much shorter. I didn't even think of
doing it that way! Biff "Peo Sjoblom" wrote in message ... The -1 does not offset the result, it tells excel what match type to use, you need all number/dates to be sorted in descending order and it will find the smallest value that is greater than or equal to lookup value, if you want to offset the result put -1 after the formula but do not change the match type =MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0)-1 -- Regards, Peo Sjoblom (No private emails please) "David" wrote in message ... Hi Biff, This works, I get the correct row as the answer, assuming the date exists in the table. But if I experiment, change the date to a date that does not exist and the last index parameter to -1, i begin to get erroneous answers. =MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields the correct row, 359. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula yields #N/A, which is expected, if i read the formulas correctly. =MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),-1) this formula yields 46, which is not what was expected at all. The date changed by one day only and I expected with the -1 parameter change would get the date just below that date (the closest date, but below it.) The date that found is 01/14/05. In the end i should be able to take todays date, subtract 365 days and find the closest date that either matches it exactly or is just a little less than that date. It is very unlikely that it would ever match exactly. -- David "Biff" wrote: Hi! Try this: =MATCH("01/07/05",INDEX(TEXT(A1:A10,"mm/dd/yy"),,1),0) Biff "David" wrote in message ... I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. -- David |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
Hi Aladin,
This seems to be working pretty well, even with dates that are approximate and I am not sure I understand why it is working, but that maybe ok. By using the "+0" and changing it to +365, I am getting the date change and approximation match. Thank you for your help. I still need to do a little tweeking, but I think I can use this formula with variables and code it too. Thanks again. -- David "Aladin Akyurek" wrote: If Date is in ascending order: =MATCH("01/11/99"+0,DateRange,1) Otherwise, replace 1 with 0. David wrote: I am doing a match function using dates. Column A has weekly dates with a Header, "Date", going back about 5 years and formated short, mm/dd/yy. Match works if I use a cell address with a date in it, it works if I use the serial number for the date, but I am having a really hard time trying to put the date in manually ie as a Text Value. The literals I have tried include 01/11/99, "01/11/99", #01/11/99". I have also tried using the DateValue function in an attempt to get the Serail number of the date, but keep getting #VALUE!, although the serial number in know by Excel and is apparent whan a cell is selected. The serial number is visible as soon as a cell is selected, but the answer does not translate down. In the end I will be trying to take a date, add 365 days to it and do a match funtion based on this calculated datebut I am having a hard time just making the function work well. Then I will trying and put it into code. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
VlLOOKUP function with MATCH | Excel Worksheet Functions | |||
Match function selecting first value it matches on exactly | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |