Match Function Not returning the correct row address

SanjayGulatiMusafir

Board Regular
Joined
Sep 7, 2018
Messages
97
I have a table that carries multiple entries of same date - Sorted in Ascending Order.

I'm using following formula to get Cell address that contains today's date

Excel Formula:
=ADDRESS(MATCH(TODAY(),Banks[Dt],1),3,4,1)

But it returns cell address 2 to 3 cells above the desired cell.
  • I have tried using "0" in match function, but that doesn't help.
  • Also, occasionally current date goes missing in the table, thus using "1" for approximate match becomes, probably, mandatory.
Can someone help me identify where I'm going wrong and also help improve the formula.

Thanks
 

Attachments

  • Screenshot 2021-02-24 at 21.17.14.png
    Screenshot 2021-02-24 at 21.17.14.png
    69.5 KB · Views: 8

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
MATCH returns the position within a range, not its row number. The two may happen to be the same if your range starts in row 1, but that won't be the case with a table since the headers are usually in row1.
 
Solution

SanjayGulatiMusafir

Board Regular
Joined
Sep 7, 2018
Messages
97
MATCH returns the position within a range, not its row number. The two may happen to be the same if your range starts in row 1, but that won't be the case with a table since the headers are usually in row1.

Hi RoryA

Exactly that is happening. I realized it after you replied. Actually my range starts from Row 5, that's why it was giving the error of exactly five rows.

Now probably I know the work around

Thanks

 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You might also want to replace the 3 in your formula with COLUMN(Banks[Dt]) so it will adjust automatically if you move the table.
 

Forum statistics

Threads
1,148,145
Messages
5,745,049
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top