Match Function And Display Row Number

  • Thread starter Thread starter CecesWorking
  • Start date Start date
C

CecesWorking

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.
 
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
 
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.
 
Back
Top