# Nested Xlookup Function

Copper Contributor

# Nested Xlookup Function

Hi,

Please can someone help me amend my formula to include the type to be IN/OUT.

E.g. Factor = Loss, Client = Andrew, Type = OUT would return -2 as my current formula:

=XLOOKUP(K4,B3:B5,XLOOKUP(J4,C2:G2,C3:G5))

Returns -1

Any help greatly appreciated

4 Replies

# Re: Nested Xlookup Function

=INDEX(\$D\$4:\$G\$5, MATCH(K4, \$B\$4:\$B\$5, 0), MATCH(1, (\$D\$2:\$G\$2=J4)*(\$D\$3:\$G\$3=I4), 0))

# Re: Nested Xlookup Function

@YLB_8001 If you want to stick with the nested XLOOKUP method, try the following:

``=XLOOKUP(K4, B4:B5, XLOOKUP(1, (J4=D2:G2)*(I4=D3:G3), D4:G5))``

Another option is to use SUMIFS with XLOOKUP in the sum_range argument:

``=SUMIFS(XLOOKUP(K4, B4:B5, D4:G5), D2:G2, J4, D3:G3, I4)``

# Re: Nested Xlookup Function

thank you for this Hans - very helpful

# Re: Nested Xlookup Function

thank you djclements, this was very helpful. I am now using your Sumifs formula as this seems a lot cleaner. :)