Forum Discussion
I am losing my mind, please help...
I want to thank you for trying very hard to explain your issue, but unfortunately I think I am still lost as to what you really need. My best guess is that if there is a "S" in col D you need the value from the corresponding line that has the "P". Assuming any line with an "S" will have a corresponding line with a "P" then a simple answer might be:
XLOOKUP("P"& [@[KIT DOCUMENT NBR]], [P / S] & [KIT DOCUMENT NBR], [AUTH QTY], 0)if that isn't actually formatted as a table then I recommend you DO format it as a Table and use table references like above instead of referencing whole columns of data. But if you insist on not formatting as a table then:
XLOOKUP("P"& N6, D:D & N:N, H:H, 0)
I want to thank you for trying very hard to explain your issue, but unfortunately I think I am still lost as to what you really need. My best guess is that if there is a "S" in col D you need the value from the corresponding line that has the "P". Assuming any line with an "S" will have a corresponding line with a "P" then a simple answer might be:
XLOOKUP("P"& [@[KIT DOCUMENT NBR]], [P / S] & [KIT DOCUMENT NBR], [AUTH QTY], 0)if that isn't actually formatted as a table then I recommend you DO format it as a Table and use table references like above instead of referencing whole columns of data. But if you insist on not formatting as a table then:
XLOOKUP("P"& N6, D:D & N:N, H:H, 0)
Thank you for taking the time to try and help me. I will try to explain it better. If a kit document number is the same it means that one is a substitute and the other is a prime. The substitute auth qty will always be zero, but in reality the prime auth qty will represent the auth qty for the substitute. Because the document is sorted by document number, in my original equation i used the following (one row down) auth qty, but this equation doesn't work for when the prime might be in the row above or two rows below (in the case of multiple substitutes). From looking at your formula it looks like it will work. I will try it and let you know. mtarler