Nested Xlookup Function

Question

Hi,&nbsp;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&nbsp;Any help greatly appreciated

hansvogelaar · Answer

YLB_8001&nbsp;
=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))

djclements · Answer

YLB_8001&nbsp;If you want to stick with the nested XLOOKUP method, try the following:&nbsp;=XLOOKUP(K4, B4:B5, XLOOKUP(1, (J4=D2:G2)*(I4=D3:G3), D4:G5))&nbsp;For more information, please see:&nbsp;https://exceljet.net/formulas/xlookup-with-multiple-criteria&nbsp;&nbsp;Another option is to use SUMIFS with XLOOKUP in the sum_range argument:&nbsp;=SUMIFS(XLOOKUP(K4, B4:B5, D4:G5), D2:G2, J4, D3:G3, I4)&nbsp;

ylb_8001 · Answer

thank you for this Hans - very helpful

ylb_8001 · Answer

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

Forum Discussion

Nested Xlookup Function

Share

Resources