# xlookup

Copper Contributor

# xlookup

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

# Re: xlookup

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

# Re: xlookup

Unfortunately, no that doesn't work either.

# Re: xlookup

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

# Re: xlookup

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

# Re: xlookup

Thank you so much that worked perfectly