Return CELL address if If Range greater than TODAY

[Tony_Sco]

I am trying to use the following to find a minimum location within a range "balance" where the "dateRange" is greater than or equal to today.

This formula, but it says I have too many arguments:
=MIN(IF((dateRange>=TODAY()),CELL("address",balance,MATCH(balance,balance,0)))

 

[Eric W]

Welcome to the MrExcel forum!

I'm not sure why you want the address, if you can get the value by itself. But try this:

Book1
	A	B	C	D	E
1	Date	Balance		Min Balance after today	Address
2	30-Jan	1		4	$B$5
3	31-Jan	2
4	1-Feb	3
5	2-Feb	4
6	3-Feb	5
7
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(15,6,balance/(dateRange>TODAY()),1)
E2	E2	=ADDRESS(AGGREGATE(14,6,ROW(balance)/(balance=D2)/(dateRange>TODAY()),1),COLUMN(balance))

Named Ranges
Name	Refers To	Cells
balance	=Sheet1!$B$2:$B$6	D2:E2
dateRange	=Sheet1!$A$2:$A$6	D2:E2

 

[JamesCanale]

How about this:

=CELL("address",OFFSET(A1:A16,MATCH(SUMPRODUCT(MIN(IF(A1:A16<TODAY(),99999,A1:A16))),A1:A16)-1,0))

I see this is wrong. It's finding the address of the smallest date equal or after today.

 

[Tony_Sco]

Welcome to the MrExcel forum!

I'm not sure why you want the address, if you can get the value by itself. But try this:

Book1
	A	B	C	D	E
1	Date	Balance		Min Balance after today	Address
2	30-Jan	1		4	$B$5
3	31-Jan	2
4	1-Feb	3
5	2-Feb	4
6	3-Feb	5
7
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(15,6,balance/(dateRange>TODAY()),1)
E2	E2	=ADDRESS(AGGREGATE(14,6,ROW(balance)/(balance=D2)/(dateRange>TODAY()),1),COLUMN(balance))

Named Ranges
Name	Refers To	Cells
balance	=Sheet1!$B$2:$B$6	D2:E2
dateRange	=Sheet1!$A$2:$A$6	D2:E2

Welcome to the MrExcel forum!

I'm not sure why you want the address, if you can get the value by itself. But try this:

Book1
	A	B	C	D	E
1	Date	Balance		Min Balance after today	Address
2	30-Jan	1		4	$B$5
3	31-Jan	2
4	1-Feb	3
5	2-Feb	4
6	3-Feb	5
7
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(15,6,balance/(dateRange>TODAY()),1)
E2	E2	=ADDRESS(AGGREGATE(14,6,ROW(balance)/(balance=D2)/(dateRange>TODAY()),1),COLUMN(balance))

Named Ranges
Name	Refers To	Cells
balance	=Sheet1!$B$2:$B$6	D2:E2
dateRange	=Sheet1!$A$2:$A$6	D2:E2

Hi Eric,
That solution works great, incredibly fast reply too... Many thanks!!
The reason I wanted the address is so I can use this macro to goto the cell location in a very long list:

Sub Go_To_Lowest_Balance_Cell()
Application.Goto Sheets("Check Reg_Budget").Range(Sheets("Check Reg_Budget").Range("U3").Value)
End Sub

Tony

 

[Eric W]

Ah, OK! There are other VBA ways to do that without needing the formula, but that works fine. You can also go the other way, and do it on the sheet itself without VBA at all:

=HYPERLINK("#'Check Reg_Budget'!"&E2,"Min location")

But of course it depends on what else you've got going on in your workbook.

Anyway, glad we could help!

 

[Tony_Sco]

Thanks, I'll try that too!

With you initial solution, I see that it returns the last cell in the column with the lowest amount.
i.e.
$932.77
$1,914.09
$1,603.11
$511.79
$511.79
$511.79
$511.79 < this cell is returned
$2,229.79

I was just wondering why not the first occurrence?

 

[Eric W]

If you want the first occurrence, change the 14 to 15 like this:

=ADDRESS(AGGREGATE(15,6,ROW(balance)/(balance=D2)/(dateRange>TODAY()),1),COLUMN(balance))

The AGGREGATE makes a list of matching rows, then 14 means to take the max row, and 15 means take the minimum row.

 

[Tony_Sco]

Eric,
You are a true excel magician. Can't thank you enough!!