My specific use case is kind of complicated, but I am having a problem with my arrayformula.
In a nutshell, I've made a Google Sheet to help out my coworkers, and I also added a reference tab which lists out all of the formulas I used, so they can learn more about them if they so choose.
I realized as I was updated one of the formulas today, that I didn't want to have to go in and manually edit the formula on the reference page as well, and it would be good to be able to just grab the cell reference by the title of the formula, then use FORMULATEXT() and it will display the correct formula. Simple! That was 5 hours ago.
Here is my code for grabbing the cell references:
=ARRAYFORMULA({
"Formulas:";
IF(
$B$2:$B = "", "",
IFERROR(CELL("ADDRESS", INDEX(Overview!$C$11:$C, MATCH($B$2:$B, Overview!$C$11:$C, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Overview!$E$11:$E, MATCH($B$2:$B, Overview!$E$11:$E, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Overview!$G$11:$G, MATCH($B$2:$B, Overview!$G$11:$G, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$A$1, MATCH($B$2:$B, Metrics!$A$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Reference!$A$1, MATCH($B$2:$B, Reference!$A$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Sheet1!$A$5, MATCH($B$2:$B, Sheet1!$A$5, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Sheet1!$A$5, MATCH($B$2:$B, Sheet1!$C$5, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$D$1, MATCH($B$2:$B, Metrics!$D$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$E$1, MATCH($B$2:$B, Metrics!$E$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$I$1, MATCH($B$2:$B, Metrics!$I$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$J$1, MATCH($B$2:$B, Metrics!$J$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!KE$1, MATCH($B$2:$B, Metrics!$K$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$L$1, MATCH($B$2:$B, Metrics!$L$1, 0), 1)),
IFERROR(CELL("ADDRESS", INDEX(Metrics!$M$1, Match($B$2:$B, Metrics!$M$1, 0), 1)),
"Not Found"
)))))))))))))))
})
$B$2:$B is where the Name of the Formulas are. On my overview page, all of my formulas are in one of 3 columns: C, E, and G. So I can get by with just 3 INDEX and MATCH combos here (technically, I have not tried doing each one individually, because there's 21 of them on the Overview page, and I don't want to type all that out; also because, as I explain below, this should be working fine). All the others are formulas at the top of the column that go all the way down, so I need a separate combo for each.
For some reason, it is only grabbing the correct reference for the first row (Overview!$C$13), and then using that reference for all the other rows, and I have no idea why. I think it has something to do with INDEX, IF I take out CELL and INDEX, The MATCH works as I'd expect, all the way down the column. But as soon as I add INDEX back in, it just gives the row offset for the first formula (3, since the first formula is actually in Overview!$C$13),
The crazy thing to me, is that this actually works, just not in the arrayformula. If I input this starting at the IFERRORs (and change all the $B$2:$Bs to $B2), it will give the correct answer for that one row, and then if I click and drag the formula down the column, it gives the correct reference for all the rows. But I don't want to do that, because it's ugly, and my formula is already ugly enough.
I don't think there's anything wrong with my logic, I just must be missing something about how INDEX functions or something