Allocating cost based on balance on PO line

[fayo2009]

Good morning

I am looking for a formula that I can incorporate into an xlookup that will allocate the invoice value up to the value on the first PO line 1 before it uses the value in PO line 2. the xlookup is currently set to select the latest PO line number.

Invoice line	Net amount
123	20,000.00
124	506.50
125	243.42
126	189.77
127	435.54
128	5,000.00
129	55.71
130	350.28
131	22,000.00
132	6.34
133	0.05
134	411.35
135	174.05
136	605.78
137	1,063.53
138	55.69
139	200.54
140	0.68
141	76.91
PO Number	PO Line	PO Value
345678	1	50,000.00
345678	2	600,000.00

 

[Joe4]

I am afraid that your question does not make sense to me (and I am guessing others, as well).

It may be helpful if you actually walk us through your example, and explain the logic as you work through it, and show us how to arrive at your expected result.

 

[fayo2009]

Thanks Joe4 please see logic below .

Invoice line	Net amount	Allocated PO Number	PO Line	PO Line no	preferred PO line
123	20000	345678	=XLOOKUP(C2,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
124	506.5	345678	=XLOOKUP(C3,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
125	243.42	345678	=XLOOKUP(C4,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
126	189.77	345678	=XLOOKUP(C5,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
127	435.54	345678	=XLOOKUP(C6,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
128	5000	345678	=XLOOKUP(C7,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
129	55.71	345678	=XLOOKUP(C8,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
130	350.28	345678	=XLOOKUP(C9,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
131	22000	345678	=XLOOKUP(C10,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
132	6.34	345678	=XLOOKUP(C11,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
133	0.05	345678	=XLOOKUP(C12,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
134	411.35	345678	=XLOOKUP(C13,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
135	174.05	345678	=XLOOKUP(C14,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
136	605.78	345678	=XLOOKUP(C15,$A$26:$A$27,$B$26:$B$27,,,-1)	2	1
137	1063.53	345678	=XLOOKUP(C16,$A$26:$A$27,$B$26:$B$27,,,-1)	2	2
138	55.69	345678	=XLOOKUP(C17,$A$26:$A$27,$B$26:$B$27,,,-1)	2	2
139	200.54	345678	=XLOOKUP(C18,$A$26:$A$27,$B$26:$B$27,,,-1)	2	2
140	0.68	345678	=XLOOKUP(C19,$A$26:$A$27,$B$26:$B$27,,,-1)	2	2
141	76.91	345678	=XLOOKUP(C20,$A$26:$A$27,$B$26:$B$27,,,-1)	2	2
PO Number	PO Line	PO Value
345678	1	50000
345678	2	600000

 

[Joe4]

I am still looking for a detailed explanation.

 

[fayo2009]

I am still looking for a detailed explanation.

No problem and thanks for looking into it

I used the xlookup formular to look up the PO line number based on the PO number assigned to the various invoices. The xlookup formular is set to select the last PO line. However I am looking for a formula that will select the1st PO line and allocate as much invoice line value. when there is not enough PO value on the 1st PO line it would move on and use the balance on the second PO line.

 

[Joe4]

OK, I think I see what you are trying to do now.
I am pretty sure that there are probably ways to do it with formulas, but I am having a tough time getting it to work out the way that I want, since you are really matching on two values (Number and Value). I will have to keep playing around with it. Usually, when I get stuck trying to figure out a formula, I opt for a VBA solution.

 

[Joe4]

In your actual/real data, do you have multiple different PO Numbers in the summary below to choose from?
If it is always just one, and it always matches the data in your table, it would be much easier.
I am just trying to determine if your example is realistic, or a simplified version of the actual data.

 

[fayo2009]

In your actual/real data, do you have multiple different PO Numbers in the summary below to choose from?
If it is always just one, and it always matches the data in your table, it would be much easier.
I am just trying to determine if your example is realistic, or a simplified version of the actual data.

Yes in the real actual data there are multiple PO numbers and PO lines. The PO number is mapped to the invoice line based on the service type (I have already have formula for that). I am looking for a formula that will select the1st PO line and allocate as much invoice line value. when there is not enough PO value on the 1st PO line it would move on and use the balance on the second PO line.

 

[fayo2009]

OK, I think I see what you are trying to do now.
I am pretty sure that there are probably ways to do it with formulas, but I am having a tough time getting it to work out the way that I want, since you are really matching on two values (Number and Value). I will have to keep playing around with it. Usually, when I get stuck trying to figure out a formula, I opt for a VBA solution.

Thanks for your help, much appreciated. I have been on it for months myself and trying to avoid VBA. the work around at the moment is to remove the additional PO lines from the table and wait until the Old one is used up.

 

[Tetra201]

Try placing the following formulas in cells C2 and D2, respectively, and drag-copying them down as needed:

=INDEX($A$26:$A$27,MATCH(TRUE,$C$26:$C$27-SUMIFS($B$1:$B1,$C$1:$C1,$A$26:$A$27,$D$1:$D1,$B$26:$B$27)>=B2,0))
=INDEX($B$26:$B$27,MATCH(TRUE,$C$26:$C$27-SUMIFS($B$1:$B1,$C$1:$C1,$A$26:$A$27,$D$1:$D1,$B$26:$B$27)>=B2,0))