Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function And Display Row Number
Hello -
I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/2/09 o x x 1/3/09 o o x I have another field which auto-populates today's date when the workbook is refreshed. The formula for that is: =TEXT(TODAY(),"mm/dd/yy") I would like to use Match to find the row with the closest date to todays date. Any help would greatly be appreciated. Thank you for your time. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function And Display Row Number
Just put =TODAY() in that cell, and format it as mm/dd/yy (assume this
is cell D1), and then you could use this: =MATCH(D1,A:A) to get the row number of the date equal to or just below the date in D1. Your example shows consecutive dates, so one should match the search date exactly. Hope this helps. Pete On Jan 26, 3:29*pm, CecesWorking wrote: Hello - I have a data set similar to below Date * * Field 1 * * Field 2 * * Field 3 1/1/09 * * x * * * * * * x * * * * * * o 1/2/09 * * o * * * * * * x * * * * * * x 1/3/09 * * o * * * * * * o * * * * * * x I have another field which auto-populates today's date when the workbook is refreshed. The formula for that is: =TEXT(TODAY(),"mm/dd/yy") I would like to use Match to find the row with the closest date to todays date. Any help would greatly be appreciated. Thank you for your time. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function And Display Row Number
Say we have an unsorted list of dates in A1 thru A29.
=MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) will give you the row most closely matching today's date. NOTE: This is an array formula. It must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200829 "CecesWorking" wrote: Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/2/09 o x x 1/3/09 o o x I have another field which auto-populates today's date when the workbook is refreshed. The formula for that is: =TEXT(TODAY(),"mm/dd/yy") I would like to use Match to find the row with the closest date to todays date. Any help would greatly be appreciated. Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a function to return EXACT row number of a match | Excel Worksheet Functions | |||
Cross match data in Col A v/s Col B and display match in Col 3 | Excel Discussion (Misc queries) | |||
Function to find/display the date X number of days later? | Excel Worksheet Functions | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |