excelexcel-formula

Lookup multiple criteria


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 

Solution

  • Here is one way you could try:

    enter image description here

    =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:

    enter image description here

    =FILTER(CHOOSECOLS(B2:I3, SEQUENCE(, 3, 4)), A5=A2:A3, "")
    

    Or,

    =XLOOKUP(A5, A2:A3, FILTER(B2:I3, B1:I1="FY25", ""), "")