Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search through dates | Excel Worksheet Functions | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Search for most recent date from list of dates | Excel Discussion (Misc queries) | |||
Search between two dates | Excel Discussion (Misc queries) |