Address function to return values from min row
 

I have a formula that returns the min $ value in a table that meets certain
criteria:

=MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16," "))

I need to be able to pull other values from that row (some are text). I'm
trying to use an address function to get the cell reference but I keep
getting #VALUE. Anyone know what I'm doing wrong below?

=ADDRESS(MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,ROW($A$5:$D$16),MIN(IF(($D$9:$D$16= $D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16," "))=$A$5:$D$16,COLUMN($A$5:$D$16),4)

Address function to return values from min row
 

Try using MATCH and INDEX

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Laury" wrote in message
...
I have a formula that returns the min $ value in a table that meets certain
criteria:

=MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))

I need to be able to pull other values from that row (some are text). I'm
trying to use an address function to get the cell reference but I keep
getting #VALUE. Anyone know what I'm doing wrong below?

=ADDRESS(MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,ROW($A$5:$D$16),MIN(IF(($D$9:$D$16= $D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,COLUMN($A$5:$D$16),4)

Address function to return values from min row
 

Thanks! That worked:

=INDEX($A$9:$D$16,MATCH(MIN(IF(($D$9:$D$16=$D1)*(( $A1-($A$9:$A$16))<31)*(($A1-($A$9:$A$16))-31),$B$9:$B$16," ")),$B$9:$B$16,0),3)

"Don Guillett" wrote:

Try using MATCH and INDEX

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Laury" wrote in message
...
I have a formula that returns the min $ value in a table that meets certain
criteria:

=MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))

I need to be able to pull other values from that row (some are text). I'm
trying to use an address function to get the cell reference but I keep
getting #VALUE. Anyone know what I'm doing wrong below?

=ADDRESS(MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,ROW($A$5:$D$16),MIN(IF(($D$9:$D$16= $D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,COLUMN($A$5:$D$16),4)

Address function to return values from min row
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Laury" wrote in message
...
Thanks! That worked:

=INDEX($A$9:$D$16,MATCH(MIN(IF(($D$9:$D$16=$D1)*(( $A1-($A$9:$A$16))<31)*(($A1-($A$9:$A$16))-31),$B$9:$B$16,"
")),$B$9:$B$16,0),3)

"Don Guillett" wrote:

Try using MATCH and INDEX

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Laury" wrote in message
...
I have a formula that returns the min $ value in a table that meets
certain
criteria:

=MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))

I need to be able to pull other values from that row (some are text).
I'm
trying to use an address function to get the cell reference but I keep
getting #VALUE. Anyone know what I'm doing wrong below?

=ADDRESS(MIN(IF(($D$9:$D$16=$D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,ROW($A$5:$D$16),MIN(IF(($D$9:$D$16= $D5)*(($A5-($A$9:$A$16))<31)*(($A5-($A$9:$A$16))-31),$B$9:$B$16,"
"))=$A$5:$D$16,COLUMN($A$5:$D$16),4)