Index Multiple Columns to correspond to specific column

[CharlieFuggle]

Hi everyone,

New to this forum so hopefully this is ok, and I explain it right.

I'm trying to find a way to auto-generate invoice amounts by using the invoice number.

Index Match works perfectly on one column, however the way the spreadsheet works, some new invoice numbers are on the same row in the table, but on the next column in the table. (same customer job)

Invoices are for example in column L-Q, but the amounts i need to pull through are on D-H.

Is there a way to make L pull through in order from D-H (so if D was blank, it would pull through E etc.)?

Any help is massively appreciated,
Charlie
Excel 2019 / MacOS

 

[Toadstool]

Hello CharlieFuggle,

I'm not sure if I follow (a worked example using XL2BB would be best) but maybe SUMIFS does that for you.

CharlieFuggle.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Invoice
2	77
3	Total			Amounts	Amounts	Amounts	Amounts	Amounts				Invoices	Invoices	Invoices	Invoices	Invoices
4	15			1	2	3	4	5				88	88	66	55	88
5				6	7	8	9	10				77	77			88
6				1	1	1	1	1				55		55		77
7				1	1	1	1	1				22	33	44	55	66
8				1	1	1	1	1				33	11	99	66	77
9
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUMIFS($D$4:$H$99,$L$4:$P$99,$A$2)

 

[CharlieFuggle]

Hi ToadStool,

Yeah thought it would be confusing to explain, here's an example.

I have customers who pay different amounts, some pay 25%, 50% and or 100%.

So for example, client 1 paid 25% (invoice relates to that is 1678) and then paid 100% (invoice relating to that is 3100).

Client 2 however paid 100% so only 1 invoice needed (1679).

Bene Test 1.xlsm
	A	B	C	D	E	F	G	H	I	J
4	Client	Column3	25% Dep	50% Dep	100% Paid	Total Paid	Total Owed	Invoice 1	Invoice 2	Column2
5	client 1		$5,000		$20,000	$20,000	$0	1678	3100
6	client 2				$24,000	$24,000	$0	1679
7	client 3		$7,000	$14,000	$28,000	$28,000	$0	1680	2010	2222
8	client 4			$16,000	$32,000	$32,000	$0	1681	2011
9	client 5			$18,000	$36,000	$36,000	$0	1682	2090
PF

Not sure there is a way to pull through that if i search invoice number 3100 on my invoice template, it will pull through the second payment?

 

[Marcelo Branco]

Maybe something like this

I used two helper columns (B and C) to make things easier

Pasta1
	A	B	C	D	E	F	G	H	I	J
1	Invoice	Row	Column	Result
2	3100	1	2	20000
3	1678	1	1	5000
4	1680	3	1	7000
5	1682	5	1	18000
6	2011	4	2	32000
7	1679	2	1	24000
8
9	Client	Column3	25% Dep	50% Dep	100% Paid	Total Paid	Total Owed	Invoice 1	Invoice 2	Column2
10	client 1		5000		20000	20000	0	1678	3100
11	client 2				24000	24000	0	1679
12	client 3		7000	14000	28000	28000	0	1680	2010	2222
13	client 4			16000	32000	32000	0	1681	2011
14	client 5			18000	36000	36000	0	1682	2090
Plan2

Cell Formulas
Range	Formula
B2:B7	B2	=AGGREGATE(15,6,(ROW($H$10:$J$14)-ROW($H$10)+1)/($H$10:$J$14=A2),1)
C2:C7	C2	=AGGREGATE(15,6,(COLUMN($H$10:$J$14)-COLUMN($H$10)+1)/($H$10:$J$14=A2),1)
D2:D7	D2	=INDEX(C$10:E$14,B2,MATCH(C2,INDEX(SUBTOTAL(2,OFFSET(INDEX($C$10:$C$14,B2),,,,COLUMN($C$10:$J$14)-COLUMN($C$10)+1)),),0))

Hope this helps

M.