Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Return CELL address if If Range greater than TODAY

0

I am currently using the following to find a minimun location within column "X" from cell 5 to cell 5000:

=CELL("address",INDEX($X$5:$X$5000,MATCH(MIN($X5:$X$5000),$X$8:$X$5000,0)))

I use: {=MIN(IF(dateRange>TODAY(),IF(balance=MIN(IF(dateRange>TODAY(),balance)),dateRange)))} to return the date that the minimum amount occurs.

I am also using the following to determin the minimum amount in colum "X"

{=MIN(IF(L5:L10000>=TODAY(),X5:X10000))}

Is it possible to combine the these formulas to return the location of the minimun amount in colum "X" that is greater than or equal to TODAY?

excel
MS Office Version: 2016
posted by: tscocozza on Sun Jan 31, 2021 at 10:47 am
Answer
Discuss

Answers

0

Hello tscocozza and welcome to the Forum.

The array formula below returns the address of the cell with the lowest date greater or equal to today in cells A1:A10 

{=CELL("address",OFFSET($A$1,MATCH(MIN(IF($A1:$A$10>=TODAY(),$A1:$A$10)),$A$1:$A$10,0)-1,0)))}

Revision 1: Attached a file to demonstrate the above formula. Added a note saying "when entering the formula, use Ctrl+Shift+Enter (NOT Enter or it won't work)"

Hope a modified version of this can work for you.

posted by: John_Ru (rep: 6152) on Sun Jan 31, 2021 at 11:37 am
Discuss

Discussion

Hi John,
Thanks for your reply. However, when I enter todays date in cell A1 and dollar amounts in cells A2 thru A10, the formula you sent returns "$A$1". Maybe I'm doing somethong wrong? 
I tried this formula, but it says I have too many arguments:
=MIN(IF((dateRange>=TODAY()),CELL("address",balance,MATCH(balance,balance,0)))
tscocozza Feb 1, '21 at 10:16 am
Hi

If today's date is in A1 then the formula SHOULD return $A$1 (since it's the earliest date meeting the >= criteria). Try putting a date from last month in A1 but today's date in A8 say (when it should return $A$8- if not you haven't entered it as an array function- that reuires you to put the formula in the cell then press Ctrl+Shit+Enter rather than Enter; and the same if you edit it)
John_Ru (rep: 6152) Feb 1, '21 at 10:24 am
I'll revise my Answer to include a file to demo this.
John_Ru (rep: 6152) Feb 1, '21 at 10:25 am
See Revision 1 to Answer (and attached file)
John_Ru (rep: 6152) Feb 1, '21 at 10:32 am
Note that my Answer is for greater than or equal to  today's date (like your MIN formula). If you want just greater than remove the = from >= and use this:
{=CELL("address",OFFSET($A$1,MATCH(MIN(IF($A1:$A$10>TODAY(),$A1:$A$10)),$A$1:$A$10,0)-1,0)))}
, again using Ctrl+Shift+Enter.
John_Ru (rep: 6152) Feb 1, '21 at 10:42 am
Hello again. Did you try my file?
John_Ru (rep: 6152) Feb 2, '21 at 4:27 am
Did you succeed?
John_Ru (rep: 6152) Feb 8, '21 at 4:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login