SOLVED

Summing across XLOOKUPs

%3CLINGO-SUB%20id%3D%22lingo-sub-2355480%22%20slang%3D%22en-US%22%3ESumming%20across%20XLOOKUPs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355480%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20do%20a%20look-up%20between%20two%20sets%20of%20SKU%2Fquantities%20which%20do%20not%20have%20entirely%20the%20same%20SKU%20information%20between%20the%20arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jparris_0-1621010187624.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280760i7EBCCAC4250E91A2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jparris_0-1621010187624.png%22%20alt%3D%22jparris_0-1621010187624.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20long%20as%20the%20fields%20between%20both%20sets%20of%20SKUs%20are%20the%20same%2C%20a%20simple%20XLOOKUP%20does%20the%20job%20handily%20for%20giving%20me%20a%20difference%20between%20them%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%3DXLOOKUP(D2%2CWoWPrevSKU%2CWoWCurr90DN-WoWPrev90DN)%22%20(Left%20side%20array%20is%20Prev%5Bious%5D%2C%20right%20side%20is%20Curr%5Bent%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20get%20to%20a%20point%20where%20the%20SKUs%20don't%20align%2C%20the%20sums%20go%20crazy%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20appear%20in%20that%20Trend%20cell%20is%20for%20the%20difference%20(either%20%2B%2F-)%20between%20the%2090DFN%20cells%20in%20each%20array%20where%20the%20SKU%20is%20the%20same%20in%20both%20arrays.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2355480%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2355519%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20across%20XLOOKUPs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054081%22%20target%3D%22_blank%22%3E%40jparris%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(D2%2CWoWCurrSKU%2CWoWCurr90DN)-XLOOKUP(D2%2CWoWPrevSKU%2CWoWPrev90DN)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2355569%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20across%20XLOOKUPs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355569%22%20slang%3D%22en-US%22%3EWell%2C%20that's%20what%20I%20get%20for%20not%20thinking%20the%20process%20out%20further%20than%20trying%20to%20cram%20it%20all%20into%20one%20formula%20field!%20LOL%20Thank%20you%2C%20Hans!%3C%2FLINGO-BODY%3E
New Contributor

I'm trying to do a look-up between two sets of SKU/quantities which do not have entirely the same SKU information between the arrays.

 

jparris_0-1621010187624.png

 

As long as the fields between both sets of SKUs are the same, a simple XLOOKUP does the job handily for giving me a difference between them: 

 

"=XLOOKUP(D2,WoWPrevSKU,WoWCurr90DN-WoWPrev90DN)" (Left side array is Prev[ious], right side is Curr[ent])

 

Once I get to a point where the SKUs don't align, the sums go crazy wrong.

 

What I want to appear in that Trend cell is for the difference (either +/-) between the 90DFN cells in each array where the SKU is the same in both arrays.

2 Replies
best response confirmed by jparris (New Contributor)
Solution

@jparris 

 

=XLOOKUP(D2,WoWCurrSKU,WoWCurr90DN)-XLOOKUP(D2,WoWPrevSKU,WoWPrev90DN)

Well, that's what I get for not thinking the process out further than trying to cram it all into one formula field! LOL Thank you, Hans!