Forum Discussion
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,XLOOKU...
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))
For more information, please see: https://exceljet.net/formulas/xlookup-with-multiple-criteria
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)
- thank you djclements, this was very helpful. I am now using your Sumifs formula as this seems a lot cleaner. 🙂
