xlookup

Copper Contributor

Hi All

I am really new to this so apologize but i am trying to add up a list of values based on 3 criteria's using the below formula and it is returning a value error but if i take out the 3 criteria it is returning the sum but for all of the values because i have removed the 3rd criteria, should also mention the first 2 criteria's are horizontal and the third is vertical hence the transpose bit in the formula any suggestions would be really great thank you   

 

=SUM(XLOOKUP(1,(Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(TRANSPOSE(Apr!$K$5:$K$24)=Sheet1!E2),Apr!$N$5:$AG$22))

5 Replies

@trevor935 

How about

 

=SUMPRODUCT((Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(TRANSPOSE(Apr!$K$5:$K$24)=Sheet1!E2),Apr!$N$5:$AG$22)

@Hans Vogelaar 

 

Unfortunately, no that doesn't work either.

 

@trevor935 

Try this then:

 

=SUMPRODUCT((Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(Apr!$K$5:$K$24=Sheet1!E2),Apr!$N$5:$AG$24)

 

Please note that I used Apr!$N$5:$AG$24 to match the size of Apr!$K$5:$K$24

@trevor935 

The TRANSPOSE must be wrong!  I suspect you should be using two XLOOKUPS.  The first restricts the search to a column and the second searches within it for the cell to return.

= XLOOKUP(
    Apr!E2, 
    Apr!$K$5:$K$22, 
    XLOOKUP(
      1,
      (Apr!$N$2:$AG$2=Apr!A2)*(Apr!$N$4:$AG$4=Apr!$F$1),
      Apr!$N$5:$AG$22
    )
  )

@Hans Vogelaar 

 

Thank you so much that worked perfectly