#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I NEED HELP with the SPELLNUMBER Function vag Excel Worksheet Functions 0 June 21st 05 08:17 AM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
VlLOOKUP function with MATCH Amnon Wilensky Excel Worksheet Functions 2 June 6th 05 07:38 PM
Match function selecting first value it matches on exactly Paul K. Excel Worksheet Functions 1 February 24th 05 09:57 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 12:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"