Search dates
 

Hi,

I am new to this forum so apologies if there are any ground rules I have missed.

I have a spreadsheet with dates and percentage. I want to be able to search the data to retrieve a percentage.

For example, if I want to know what result was valid on 16 March, the answer would be 60.0% as the latest result up to this date was 60.0% on 4 March 09.

I would like to be able to put any date in a cell and in the next cell the most recent result for that date will appear.

I hope this makes sense.

I have attached a sample screenshot of the spreadsheet which may make more sense!

Thanks,

Nick

Search dates
 

With the date to look up in D1, this formula will return the percentage from
column B
=INDEX(B2:B6,MATCH(D1,A2:A6,-1)+1)
Please test it with more data

If you data had been sorted with dates in ascending order, a much simpler
formula could be used
=VLOOKUP(D1,A2:B6,2,TRUE)

But read Help about MATCH and VLOOKUP to check that they need you criteria
Suppose the test date was 10/Aug/2009 and the table had data for the 9th and
the 11th, which one do you want?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"WELSH_EXCEL" wrote in message
...

Hi,

I am new to this forum so apologies if there are any ground rules I
have missed.

I have a spreadsheet with dates and percentage. I want to be able to
search the data to retrieve a percentage.

For example, if I want to know what result was valid on 16 March, the
answer would be 60.0% as the latest result up to this date was 60.0% on
4 March 09.

I would like to be able to put any date in a cell and in the next cell
the most recent result for that date will appear.

I hope this makes sense.

I have attached a sample screenshot of the spreadsheet which may make
more sense!

Thanks,

Nick


+-------------------------------------------------------------------+
|Filename: screenshot.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=130|
+-------------------------------------------------------------------+



--
WELSH_EXCEL

Search dates
 

The following will work:

=VLOOKUP(DATE(2009,3,15),A1:B5,2)

but you must re-arrange your data in ascending date order:

10/01/09 63%
17/02/09 51%
04/03/09 60%
21/04/09 62%
10/05/09 74%

--
Gary''s Student - gsnu200855


"WELSH_EXCEL" wrote:


Hi,

I am new to this forum so apologies if there are any ground rules I
have missed.

I have a spreadsheet with dates and percentage. I want to be able to
search the data to retrieve a percentage.

For example, if I want to know what result was valid on 16 March, the
answer would be 60.0% as the latest result up to this date was 60.0% on
4 March 09.

I would like to be able to put any date in a cell and in the next cell
the most recent result for that date will appear.

I hope this makes sense.

I have attached a sample screenshot of the spreadsheet which may make
more sense!

Thanks,

Nick


+-------------------------------------------------------------------+
|Filename: screenshot.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=130|
+-------------------------------------------------------------------+



--
WELSH_EXCEL