| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A Function to Return Top 5 Values Only | Excel Worksheet Functions | |||
| How can I use the vlookup function to return a sum of the values? | Excel Discussion (Misc queries) | |||
| vlookup function return all values | Excel Worksheet Functions | |||
| How do I return the cell address of the largest of a set of values | Excel Discussion (Misc queries) | |||
| How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
Linear Mode
