Is there a formula to return results for abc+FY25? It would have to spill over 3 columns. I have tried if(or())
, xlookup
, index/match
, and combinations thereof. Perhaps helper columns? I can also do this via VBA but hoping there's a formula.
July July July FY25 FY25 FY25 YTD YTD
abc $1,301,697 $1,579,105 ($277,408) $1,134,600 10,726,548 20.1% $1,301,697 $1,579,105
xyz $1,134,673 $1,536,586 ($401,913) 32,143,670 32,143,670 20.3% $1,134,673 $3,536,586
Here is one way you could try:
=HSTACK("abc", FILTER(FILTER(B2:I3, A2:A3="abc", ""), B1:I1="FY25", ""))
Or another way, if all the column headers has a pattern of 3, 3, 2:
=FILTER(CHOOSECOLS(B2:I3, SEQUENCE(, 3, 4)), A5=A2:A3, "")
Or,
=XLOOKUP(A5, A2:A3, FILTER(B2:I3, B1:I1="FY25", ""), "")