Cannot implement INDIRECT() within an array formula
array-formulas, excel, excel-formula
Solution
The problem is caused by use of `ROW` and `COLUMN` functions. These functions return "arrays" even when only a single value, e.g. {1} rather than 1. In some contexts that's not an issue but in other cases (like here), excel has a problem converting {1} to 1 and the formulas don't work.
One possible solution is to enclose all `ROW` and `COLUMN` functions in a function like `MAX` or `SUM` which will convert for you, e.g. instead of
`ROW() - 5`
use
`SUM(ROW()) - 5`
but a better way (and the one I recommend) is to use `ROWS` or `COLUMNS` functions (with "S" on the end), so if first formula is in `Z6` use
`ROWS($Z$6:Z6)` or `COLUMNS($Z$6:Z6)`
those will both give you a value of 1 in Z6 itself but as you copy down or across they will increment by 1 each time (and they don't suffer the same drawback as `ROW` and `COLUMN`). This is a better solution anyway as it does away with your -5s and it's less susceptible to error should you delete or add rows or columns
Problem
I have a sheet named Finder at which I created a tool to search in other sheets (named #1, #2, etc). The problem I encountered with is that I can't use the INDIRECT function in some cases as described below: ``` { = IF(Finder!$B$4 = 1, INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")), SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)), "", ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), INT(COLUMN() / 3)), 1), IF(Finder!$B$4 = 2, INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")), SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100, ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1, ""), INT(COLUMN() / 3)), 1),... } ``` Conceptual form: ``` { = IF(Finder!$B$4 = 1, INDEX(using INDIRECT to address a range in another sheet successfully, SMALL(IF(ISERROR(SEARCH(cannot use INDIRECT here!)), "", Desired value), Desired item), 1), IF(Finder!$B$4 = 2, INDEX(using INDIRECT to address a range in another sheet successfully, SMALL(IF(cannot use INDIRECT here!, Desired value, ""), Desired item), 1),... } ``` I need to address a range in another sheets like below: ``` SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)),... SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100,... ``` it's OK, but I can't use INDIRECT to make the formula dynamic: ``` SMALL(IF(ISERROR(SEARCH(Finder!$F$4, INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))),... SMALL(IF(Finder!$F$4 = INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"),... ``` Thanks in advance UPDATE: The Finder tool contains many cells. The picture shows why I need to obtain a sheet name using `ROW()` function. UPDATE 2: The combobox in the Finder sheet has four items and uses $B$4 cell: 1- Approximate Search 2- Exact Search 3- Full Index 4- Specific Category Here is the full code of the formula: ``` = IF(ISERROR(IF(Finder!$B$4 = 1, INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")), SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)), "", ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), INT(COLUMN() / 3)), 1), IF(Finder!$B$4 = 2, INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")), SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100, ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1, ""), INT(COLUMN() / 3)), 1), IF(Finder!$B$4 = 3, IF(MOD(COLUMN(), 3) = 0, 20 * (ROW() - 6) + COLUMN() / 3, INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))), INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")), SMALL(IF(Finder!$F$4 = '#1'!$I$2:$I$100, ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1, IF(ISERROR(SEARCH(Finder!$F$4 & ",", '#1'!$I$2:$I$100)), IF(ISERROR(SEARCH("," & Finder!$F$4, '#1'!$I$2:$I$100)), IF(ISERROR(SEARCH(", " & Finder!$F$4, '#1'!$I$2:$I$100)), "", ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1)), INT(COLUMN() / 3)), 1))))), " ", IF(Finder!$B$4 = 1, INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")), SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)), "", ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), INT(COLUMN() / 3)), 1), IF(Finder!$B$4 = 2, INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")), SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100, ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1, ""), INT(COLUMN() / 3)), 1), IF(Finder!$B$4 = 3, IF(MOD(COLUMN(), 3) = 0, 20 * (ROW() - 6) + COLUMN() / 3, INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))), INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")), SMALL(IF(Finder!$F$4 = '#1'!$I$2:$I$100, ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1, IF(ISERROR(SEARCH(Finder!$F$4 & ",", '#1'!$I$2:$I$100)), IF(ISERROR(SEARCH("," & Finder!$F$4, '#1'!$I$2:$I$100)), IF(ISERROR(SEARCH(", " & Finder!$F$4, '#1'!$I$2:$I$100)), "", ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1), ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1)), INT(COLUMN() / 3)), 1))))) ```