In my spreadsheet here I calculate E
column by dragging E1
cell down to populate with values E2-E23
cells. Is there a way to use ArrayFormula
for my specific case? I have read a couple of Stackoverflow posts that ArrayFormula
does not work with Index
, I don't know if that is correct, but if yes, what can I do?
You may try:
=map(A1:A23,B1:B23,lambda(a,b,filter(filter(H2:AB21,H1:AB1=b),G2:G21=a)))
OR
=map(A1:A23,B1:B23,lambda(a,b,index(H2:AB21,xmatch(a,G2:G21),xmatch(b,H1:AB1))))