# Returning Debtors Balance if Student is youngest in family

#### Stef56

##### New Member
I would like to only return the Family Debtor Balance against the youngest student, so that the total database = my Debtors Control account. For Example:

 FAMILY CODE Class STUD ORDER BALANCE STEFJ P1 3 1000 DOCHM P1 1 2000 WEARA S2 1 1000 MCARS S3 2 1500 LAIRD P2 1 1800 STEFJ P5 1 1000 LAIRD P3 2 1800 STEFJ P2 4 1000 MCARS S4 1 1500 STEFJ P6 2 1000

<tbody>
</tbody>
Do I need to write a formula to look up the family code for multiples and if there are multiple children, only return the "balance" in another column if it is the highest number e.g. 3rd child is youngest? Any ideas would be appreciated. Thank you

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I've got to this stage =INDEX(\$D\$2:\$D\$10,MATCH(MAX(ISNUMBER(SEARCH(B2,\$C\$2:\$C\$10))*RIGHT(\$C\$2:\$C\$10,1)),ISNUMBER(SEARCH(B2,\$C\$2:\$C\$10))*RIGHT(\$C\$2:\$C\$10,1),0) (with array curly brackets), which gives me the correct Balance returning against the highest number ie. MAX. I now need the other children to be zero.

I've added a column which includes Family Code and Stud Order which is C2-C10 in above formula. I think I need an if statement !!

Let me know if this is what you want. If not tell me what results you want and the criteria to arrive at that result.

Excel 2010
ABCD
1FAMILY CODEClassSTUD ORDERBALANCE
2STEFJP13100
3DOCHMP11200
4WEARAS21300
6LAIRDP21500
7STEFJP51600
8LAIRDP32700
9STEFJP24800
11STEFJP621000
12
13Family Code to Lookup:MCARS
14Balance:400
Sheet2
Cell Formulas
RangeFormula
B14{=INDEX(D2:D11,SUMPRODUCT(((A2:A11=B13)*(C2:C11=MAX(IF(A2:A11=B13,C2:C11)))*(ROW(C2:C11)-ROW(C2)+1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Let me know if this is what you want. If not tell me what results you want and the criteria to arrive at that result.

Excel 2010
ABCD
1FAMILY CODEClassSTUD ORDERBALANCE
2STEFJP13100
3DOCHMP11200
4WEARAS21300
6LAIRDP21500
7STEFJP51600
8LAIRDP32700
9STEFJP24800
11STEFJP621000
12
13Family Code to Lookup:MCARS
14Balance:400

</tbody>
Sheet2

Array Formulas
CellFormula
B14{=INDEX(D2:D11,SUMPRODUCT(((A2:A11=B13)*(C2:C11=MAX(IF(A2:A11=B13,C2:C11)))*(ROW(C2:C11)-ROW(C2)+1))))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for trying to help me. I wasn't clear enough and I've changed the data to help clarify it. This is the result I would like: Only the Balance coming across to a new column if it relates to the youngest child (which is the highest number in Student Order). Hence my formula worked to make sure the highest balance came across, but I need a statement saying if the Student Order is less than the Max number returned, then Zero. Unfortunately it isn't just a data extraction of a record, but the whole column which needs to be recalculated for further manipulation by Class. There are 1100 children and 800 families. Thank you
 A B C D E Family Balance 1 FAMILY CODE Class STUD ORDER BALANCE Youngest Student 2 STEFJ STEFJ3 P1 3 100 0 3 DOCHM DOCHM1 P1 1 200 200 4 WEARA WEARA1 S2 1 300 300 5 MCARS MCARS2 S3 2 400 400 6 LAIRD LAIRD1 P2 1 500 0 7 STEFJ STEFJ1 P5 1 100 0 8 LAIRD LAIRD2 P3 2 500 500 9 STEFJ STEFJ4 P2 4 100 100 10 MCARS MCARS1 S4 1 900 0 11 STEFJ STEFJ2 P6 2 100 0 12

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

You can enter the formula once with Control Shift Enter and then copy down. Make sure the ranges reflect the actual ranges on your spreadsheet.

Excel 2010
ABCDEF
1FAMILY CODEClassSTUD ORDERFAMILY BALANCEYoungest
2Student
3DOCHMDOCHM1P11200200
4WEARAWEARA1S21300300
5MCARSMCARS2S32400400
6LAIRDLAIRD1P215000
7STEFJSTEFJ1P511000
8LAIRDLAIRD2P32500500
9STEFJSTEFJ4P24100100
10MCARSMCARS1S419000
11STEFJSTEFJ2P621000
Sheet3
Cell Formulas
RangeFormula
F3{=IF(\$D3=MAX(IF(\$A\$3:\$A\$11=\$A3,\$D\$3:\$D\$11)),\$E3,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

You can enter the formula once with Control Shift Enter and then copy down. Make sure the ranges reflect the actual ranges on your spreadsheet.

Excel 2010
ABCDEF
1FAMILY CODEClassSTUD ORDERFAMILY BALANCEYoungest
2Student
3DOCHMDOCHM1P11200200
4WEARAWEARA1S21300300
5MCARSMCARS2S32400400
6LAIRDLAIRD1P215000
7STEFJSTEFJ1P511000
8LAIRDLAIRD2P32500500
9STEFJSTEFJ4P24100100
10MCARSMCARS1S419000
11STEFJSTEFJ2P621000

</tbody>
Sheet3

Array Formulas
CellFormula
F3{=IF(\$D3=MAX(IF(\$A\$3:\$A\$11=\$A3,\$D\$3:\$D\$11)),\$E3,0)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Works perfectly. Thank you so much. I've learnt heaps trying to solve this and got carried away with lesson 1001 on youtube.

My pleasure.

If you did prefer a non Ctrl+Shift+Enter formula and you are using Excel 2010 or later ..

Excel Workbook
ABCDEF
1FAMILY CODEClassSTUD ORDERFAMILY*BALANCEYoungest
2Student
3DOCHMDOCHM1P11200200
4WEARAWEARA1S21300300
5MCARSMCARS2S32400400
6LAIRDLAIRD1P215000
7STEFJSTEFJ1P511000
8LAIRDLAIRD2P32500500
9STEFJSTEFJ4P24100100
10MCARSMCARS1S419000
11STEFJSTEFJ2P621000
Youngest Only

Replies
13
Views
1K
Replies
5
Views
677
Replies
1
Views
2K
Replies
3
Views
2K

1,220,977
Messages
6,157,179
Members
451,402
Latest member
Lyv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back