Extract values from different columns

[KPN]

I would like to extract the values from the column Total due in the different ranges.
I have used an HLOOKUP formula, but because the ranges are not in alignment I get inconsistent values from my formula.
How can this be solved?
Thanks.

JUNIOR Mark ^0 Anthony 2015.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Register No.	HOUSE	Gender	BOARDER/ DAY	B/F	FEES	Admission form	Caution money	Milk	Hair Cut	Swimming	Uniform	Tour	Medicine	Holiday package	Total Due	1st Instal.	Particulars	2nd Instal.	Particulars	3rd Instal.	Particulars	BALANCE		Total Due
2		ST.ANTHONY																							0
3		JUNIOR ONE																							0
4	J1664	AMPEIRE MERCY	F	B				20,000		1,000	20,000	30,000				71,000	41,000	rct6809	10,000	rct6821	20,000	rct6881	0		71000
5	J1666	KEMIGISHA ARIANNAH	F	D		150,000	30,000		20,000	1,000		10,000				211,000	30,000	rct6848	97,500	rct6923	83,500	rct7027/7085	0		211000
6		ST. MARK																							0
7	J1616	ARINDA MONICA	F	B					20,000	1,000	20,000	30,000	10,000	21,000	5,000	107,000	68,300	rct7022/7023	10,000	rct7214			28,700		107000
8	J1651	ATWIINE NGABOYAITU	M	B		450,000			20,000	1,000	20,000	50,000	10,000	38,300	5,000	594,300	299,800	rct6864	30,000	rct7098	264,500	rct7255/7256	0		594300
9	J1662	ISHIMWE KAMALI EGIDIA	F	B		450,000	30,000	20,000	20,000	1,000	20,000	85,000	10,000			636,000	30,000	rct1321	381,000	rct1375/1376	225,000	rct6819/7250	0		636000
10	J1667	AKAMPURIRA SHANITAH	F	D		250,000	30,000	20,000	20,000	1,000	20,000					341,000	30,000	rct1327	150,000	rct1336	161,000	rct7309	0		341000
11		TOTAL			0	1,300,000	90,000	60,000	100,000	6,000	100,000	205,000	30,000	59,300	10,000	1,960,300	499,100	0	678,500	0	754,000	0	28,700		1960300
12	FEES STATUS FOR LENT (1ST) TERM 2015																								0
13	Register No.	HOUSE	Gender	BOARDER DAY	B/F	FEES	Milk	Hair Cut	Swimming	Medicine	Uniform	Holiday package	Tour	Total Due	1st Instal.	Particulars	2nd Instal.	Particulars	3rd Instal.	Particulars	BALANCE				Particulars
14		ST. ANTHONY																							0
15		JUNIOR FOUR																							0
16	J1217	MUGORE WASE RABIA	F	B		225,000	20,000	1,000	20,000	25,150		5,000	10,000	306,150	250,000	rct1372	30,000	rct6806	26,150	rct7233	0				rct1372
17	J1531	MPABWANAYO NESTOL	M	B		450,000	20,000	1,000	20,000	13,600		5,000	10,000	519,600	519,600	rct6895					0				rct6895
18	J1607	UWASE QUEEN	F	B		450,000	20,000	1,000	20,000	23,500		5,000	10,000	529,500	300,000	rct6920	229,500	rct7154			0				rct6920
19	J1587	GWIZA OLIGA	F	B		450,000	20,000	1,000	20,000	8,500	85,000	5,000	10,000	599,500	342,300	rct6938/6941	257,200	rct7320			0				rct6938/6941
20	J1306	NYANGOMA RHINAH	F	B			20,000	1,000	20,000	5,300		5,000	10,000	61,300	61,300	rct6808					0				rct6808
21	J1599	UWITONZE RATIFA	F	B		450,000	20,000	1,000	20,000	14,750	10,000	5,000	10,000	530,750	280,000	rct7100/7203	250,750	rct7301			0				rct7100/7203
22	J1656	ARINAITWE PATIENCE	F	D	59,200	30,000	20,000	1,000		2,500		5,000	10,000	127,700	59,200	rct1314	68,500	rct7212			0				rct1314
23		ST.MARK																							0
24	J1566	MUGISHA RIGHAN	M	B		450,000	20,000	1,000	20,000	2,500		5,000	10,000	508,500	200,000	rct1382	275,000	rct7326			33,500				rct1382
25	J1551	HIRWA MABASI KETIA	F	B	120,000	450,000	20,000	1,000	20,000	13,050	10,000	5,000	10,000	649,050	160,000	rct1383	270,000	rct7135	150,000	rct7138/7333	69,050				rct1383
26	J1225	ALLY FILLS MUSONI	M	B		450,000	20,000	1,000	20,000	30,300		5,000	10,000	536,300	536,300	rct1391					0				rct1391
27	J1473	NAKAFERO QUEEN	F	B	49,400	15,000		1,000		29,500		5,000	10,000	109,900	25,000	rct7172	84,900	rct7340			0				rct7172
28	J1553	LOMIRO MARK	M	B						5,800		5,000	10,000	20,800							20,800				0
29	J1651	ISHIMWE JOEL	M	B		450,000	20,000	1,000	20,000			5,000	10,000	506,000	506,000	rct1343					0				rct1343
30	J1634	MANZI MAXIM	M	B		450,000	20,000	1,000	20,000			5,000	10,000	506,000	225,000	rct1397	281,000	rct7131			0				rct1397
31		TOTAL			228,600	4,320,000	240,000	13,000	220,000	174,450	105,000	70,000	140,000	5,511,050	3,464,700	0	1,746,850	0	176,150	0	123,350				0
Lent 2015

Cell Formulas
Range	Formula
P4:P5,P7:P10	P4	=SUM(E4:O4)
W4:W5,U24:U30,U16:U22,W7:W10	W4	=P4-Q4-S4-U4
E11:W11	E11	=SUM(E3:E10)
N16:N22,N24:N30	N16	=SUM(E16:M16)
E31:U31	E31	=SUM(E16:E30)
Y1:Y31	Y1	=HLOOKUP($Z$1,$A$1:$W$67,ROW(X1),FALSE)

 

[RasGhul]

Hi KPN,

I couldn't get this one without the helper column, maybe you could use this;

Book1
	A	B												N	O	P								X	Y	Z
1	Register No.	HOUSE												Medicine	Holiday package	Total Due									16
2		ST.ANTHONY																							16	$0.00
3		JUNIOR ONE																							16	$0.00
4	J1664	AMPEIRE MERCY														71000									16	$71,000.00
5	J1666	KEMIGISHA ARIANNAH														211000									16	$211,000.00
6		ST. MARK																							16	$0.00
7	J1616	ARINDA MONICA												21000	5000	107000									16	$107,000.00
8	J1651	ATWIINE NGABOYAITU												38300	5000	594300									16	$594,300.00
9	J1662	ISHIMWE KAMALI EGIDIA														636000									16	$636,000.00
10	J1667	AKAMPURIRA SHANITAH														341000									16	$341,000.00
11		TOTAL												59300	10000	1960300									16	$1,960,300.00
12	FEES STATUS FOR LENT (1ST) TERM 2015																								16	$0.00
13	Register No.	HOUSE												Total Due	1st Instal.	Particulars									14	Total Due
14		ST. ANTHONY																							14	$0.00
15		JUNIOR FOUR																							14	$0.00
16	J1217	MUGORE WASE RABIA												306150	250000	rct1372									14	$306,150.00
17	J1531	MPABWANAYO NESTOL												519600	519600	rct6895									14	$519,600.00
18	J1607	UWASE QUEEN												529500	300000	rct6920									14	$529,500.00
19	J1587	GWIZA OLIGA												599500	342300	rct6938/6941									14	$599,500.00
20	J1306	NYANGOMA RHINAH												61300	61300	rct6808									14	$61,300.00
21	J1599	UWITONZE RATIFA												530750	280000	rct7100/7203									14	$530,750.00
22	J1656	ARINAITWE PATIENCE												127700	59200	rct1314									14	$127,700.00
23		ST.MARK																							14	$0.00
24	J1566	MUGISHA RIGHAN												508500	200000	rct1382									14	$508,500.00
25	J1551	HIRWA MABASI KETIA												649050	160000	rct1383									14	$649,050.00
26	J1225	ALLY FILLS MUSONI												536300	536300	rct1391									14	$536,300.00
27	J1473	NAKAFERO QUEEN												109900	25000	rct7172									14	$109,900.00
28	J1553	LOMIRO MARK												20800											14	$20,800.00
29	J1651	ISHIMWE JOEL												506000	506000	rct1343									14	$506,000.00
30	J1634	MANZI MAXIM												506000	225000	rct1397									14	$506,000.00
31		TOTAL												5511050	3464700	0									14	$5,511,050.00
Sheet1

Cell Formulas
Range	Formula
Y1	Y1	=IF(A1="Register No.",SUMPRODUCT(--(A1:W1="Total Due")*(COLUMN(A1:W1)-COLUMN(A1)+1)),0)
Y2	Y2	=IF(A2="Register No.",SUMPRODUCT(--(A2:W2="Total Due")*(COLUMN(A2:W2)-COLUMN(A2)+1)),Y1)
Z2	Z2	=INDEX(A2:W2,,Y2)
P7:P10,P4:P5	P4	=SUM(E4:O4)
N11:P11	N11	=SUM(N3:N10)
N24:N30,N16:N22	N16	=SUM(E16:M16)
N31:P31	N31	=SUM(N16:N30)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would suffice. (I have hidden some columns to keep the mini-sheet a bit smaller)

21 08 14.xlsm
	F	G						M	N	O	P	Q					V	W	X	Y
1	FEES	Admission form						Tour	Medicine	Holiday package	Total Due	1st Instal.					Particulars	BALANCE		Total Due - 11
2																				0
3																				0
4											71000	41000					rct6881	0		71000
5	150000	30000									211000	30000					rct7027/7085	0		211000
6																				0
7								10000	21000	5000	107000	68300						28700		107000
8	450000							10000	38300	5000	594300	299800					rct7255/7256	0		594300
9	450000	30000						10000			636000	30000					rct6819/7250	0		636000
10	250000	30000									341000	30000					rct7309	0		341000
11	1300000	90000						30000	59300	10000	1960300	499100					0	28700		1960300
12																				0
13	FEES	Milk						Tour	Total Due	1st Instal.	Particulars	2nd Instal.								Total Due - 9
14																				0
15																				0
16	225000	20000						10000	306150	250000	rct1372	30000								306150
17	450000	20000						10000	519600	519600	rct6895									519600
18	450000	20000						10000	529500	300000	rct6920	229500								529500
19	450000	20000						10000	599500	342300	rct6938/6941	257200								599500
20		20000						10000	61300	61300	rct6808									61300
21	450000	20000						10000	530750	280000	rct7100/7203	250750								530750
22	30000	20000						10000	127700	59200	rct1314	68500								127700
23																				0
24	450000	20000						10000	508500	200000	rct1382	275000								508500
25	450000	20000						10000	649050	160000	rct1383	270000								649050
26	450000	20000						10000	536300	536300	rct1391									536300
27	15000							10000	109900	25000	rct7172	84900								109900
28								10000	20800											20800
29	450000	20000						10000	506000	506000	rct1343									506000
30	450000	20000						10000	506000	225000	rct1397	281000								506000
31	4320000	240000						140000	5511050	3464700	0	1746850								5511050
Total

Cell Formulas
Range	Formula
P4:P5,P7:P10	P4	=SUM(E4:O4)
W4:W5,W7:W10	W4	=P4-Q4-S4-U4
F11:G11,M11:Q11,V11:W11	F11	=SUM(F3:F10)
N16:N22,N24:N30	N16	=SUM(E16:M16)
F31:G31,M31:Q31	F31	=SUM(F16:F30)
Y1	Y1	="Total Due - "&MATCH("Total Due",F1:W1,0)
Y2:Y31	Y2	=IFNA("Total Due - "&MATCH("Total Due",F2:W2,0),INDEX(F2:W2,SUBSTITUTE(LOOKUP("Z",Y$1:Y1),"Total Due - ","")))

 

[KPN]

I though I knew Excel!!
Thanks a lot guys.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

It would still be useful if you took up my previous suggestion.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you were using Microsoft 365 you could also consider this idea.

21 08 14.xlsm
	F	G						M	N	O	P	Q					V	W	X	Y
1	FEES	Admission form						Tour	Medicine	Holiday package	Total Due	1st Instal.					Particulars	BALANCE		Total Due
2																				0
3																				0
4											71000	41000					rct6881	0		71000
5	150000	30000									211000	30000					rct7027/7085	0		211000
6																				0
7								10000	21000	5000	107000	68300						28700		107000
8	450000							10000	38300	5000	594300	299800					rct7255/7256	0		594300
9	450000	30000						10000			636000	30000					rct6819/7250	0		636000
10	250000	30000									341000	30000					rct7309	0		341000
11	1300000	90000						30000	59300	10000	1960300	499100					0	28700		1960300
12																				0
13	FEES	Milk						Tour	Total Due	1st Instal.	Particulars	2nd Instal.								Total Due
14																				0
15																				0
16	225000	20000						10000	306150	250000	rct1372	30000								306150
17	450000	20000						10000	519600	519600	rct6895									519600
18	450000	20000						10000	529500	300000	rct6920	229500								529500
19	450000	20000						10000	599500	342300	rct6938/6941	257200								599500
20		20000						10000	61300	61300	rct6808									61300
21	450000	20000						10000	530750	280000	rct7100/7203	250750								530750
22	30000	20000						10000	127700	59200	rct1314	68500								127700
23																				0
24	450000	20000						10000	508500	200000	rct1382	275000								508500
25	450000	20000						10000	649050	160000	rct1383	270000								649050
26	450000	20000						10000	536300	536300	rct1391									536300
27	15000							10000	109900	25000	rct7172	84900								109900
28								10000	20800											20800
29	450000	20000						10000	506000	506000	rct1343									506000
30	450000	20000						10000	506000	225000	rct1397	281000								506000
31	4320000	240000						140000	5511050	3464700	0	1746850								5511050
Total (2)

Cell Formulas
Range	Formula
P4:P5,P7:P10	P4	=SUM(E4:O4)
W4:W5,W7:W10	W4	=P4-Q4-S4-U4
F11:G11,M11:Q11,V11:W11	F11	=SUM(F3:F10)
N16:N22,N24:N30	N16	=SUM(E16:M16)
F31:G31,M31:Q31	F31	=SUM(F16:F30)
Y2:Y31	Y2	=LET(td,"Total Due",y,Y$1:Y1,IF(ISTEXT(F2),td,FILTER(F2:W2,INDEX(FILTER(F$1:W1,ISTEXT(y)),COUNTIF(y,td),0)=td)))