Match Function Not returning the correct row address

[SanjayGMusafir]

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

=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

 

[RoryA]

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.

 

[SanjayGMusafir]

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]

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.

 

[SanjayGMusafir]

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.

Thanks RoryA

It worked like charm.

Thanks again