I have a multiple possible arrays of values that will always be 1x1, 2x2, 3x3,...etc and I would like to get all the diagonal values of that array, such as:
1x1 array would be return the only value. 2x2 should return (row=1, col=1) value, and the (row=2, col=2) value 3x3 should return get the (row=1, col=1), (row=2,col=2), and the (row=3,col=3)
The array formula is:
`=arrayformula(SUBSTITUTE(SPLIT(D3,CHAR(10)),
RIGHT(SPLIT(D3,CHAR(10)),40),
RIGHT(SPLIT(D3,CHAR(10)),40)&" "&
if(iferror(value(RIGHT(SPLIT(D3,CHAR(10)),4)),-1)>0,"",
VLOOKUP(flatten(arrayformula(MATCH("*"&RIGHT(SPLIT(D3,CHAR(10)),40),'Lobbyists (DATA)'!A:A,0)+1)),{SEQUENCE(ROWS('Lobbyists (DATA)'!A:A),1) , 'Lobbyists (DATA)'!A:A},2,0))))`
I thought it was possible to create a repeating function that repeated a number equal to the number of rows (or columns) in the n x n array, but change the row and column index number each time the index formula repeated? The row/column index should change equal to the number of repetition it is.
For the 1st rept, it should be = index(arrayformula(...),1,1), then the 2nd should be 2,2, etc.
I tried using array lambda and MAP functions to predict the the n value and put it in the index row and column place, but to no avail. Please help!