xlookup

[trevor935]

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))

[#2]

@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)

[#3]

@HansVogelaar 

Unfortunately, no that doesn't work either.

[#4]

@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

[#5]

@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
    )
  )

[#6]

@HansVogelaar 

Thank you so much that worked perfectly