Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alternative to ADDRESS function
In Excel 2007, I am using the following formula to search for and return all
cells in the array that meet the criteria (as opposed to vlookup, which only returns one ): =IFERROR(INDEX($A$5:$A$100,SMALL(IF($B$6:$B$101=$D $3,ROW($B$1:$B$96)),ROW(1:1))),"") Then, using the ADDRESS and MATCH functions, I can return the address of all the non ERROR values output from the formula above. I need to reference a certain array of cells based upon the address of the non ERROR values. For instance, if the first non ERROR cell is A5, then I need to reference cells A5:Z7. If the next non ERROR cell is A10, then I need to reference cells A10:Z12. I need to reference those arrays because I need to use the MATCH and INDEX functions, which require an array. If I try to put =INDEX(ADDRESS(...):ADDRESS(...)...) that doesn't work. I'm sure there are many alternate routes to get where I need to go, but I can't seem to figure them out. Thanks for you help. DoubleZ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alternative to ADDRESS function
The Address() function returns a *text* reference.
Wrap it in the Indirect() function to make it a viable cell reference. Say H2 contains 50. =2*Address(2,8) returns a #Value! error. =2*Indirect(Address(2,8)) returns 100. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "DoubleZ" wrote in message ... In Excel 2007, I am using the following formula to search for and return all cells in the array that meet the criteria (as opposed to vlookup, which only returns one ): =IFERROR(INDEX($A$5:$A$100,SMALL(IF($B$6:$B$101=$D $3,ROW($B$1:$B$96)),ROW(1:1))),"") Then, using the ADDRESS and MATCH functions, I can return the address of all the non ERROR values output from the formula above. I need to reference a certain array of cells based upon the address of the non ERROR values. For instance, if the first non ERROR cell is A5, then I need to reference cells A5:Z7. If the next non ERROR cell is A10, then I need to reference cells A10:Z12. I need to reference those arrays because I need to use the MATCH and INDEX functions, which require an array. If I try to put =INDEX(ADDRESS(...):ADDRESS(...)...) that doesn't work. I'm sure there are many alternate routes to get where I need to go, but I can't seem to figure them out. Thanks for you help. DoubleZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
non-VBA Alternative to SUBTITUTE function | Excel Worksheet Functions | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
alternative function to sumif | Excel Discussion (Misc queries) | |||
Alternative for IF function | Excel Discussion (Misc queries) | |||
Excel If function alternative | Excel Worksheet Functions |