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

 

[fayo2009]

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))

Many Thanks, It didnt work.

 

[Tetra201]

Could you please elaborate on what exactly "didn't work"? -- In my test file, the formulas produced the desired results for the dataset from Post #3.

 

[Tetra201]

I just noticed that you already have a formula for Allocated PO Number. So, here is an updated formula for mapping PO Line to the Invoice Line (cell D2):

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