Showing next sibling on invoice

[ympiano]

Hi guys,

Could you please assist me with this querry. I have tried various options but I cant seem to nut out one that works consistantly.

File attached will explain where I require assistance:
1. Cell I10 is where I input the student number - the result is indexed from another sheet called 'database' and this result is shown in cell B19 and cell c19 indexes against this number cell B19 to bring up the students pricing structure from the database.
2. Cell D13 indicates whether this student has a sibling, this is where I am stuck!
I have used =INDEX(O$8:O$175,SMALL(IF(N$8:N$175=I$10,ROW(N$8:N$175 )-ROW($N$8)+1),ROW(A2))) (in array) but I need to add an 'if' statement in there somewhere to indicate if cell I11 is not a sibling then C20 is blank but if it is a sibling then to index to find the next sibling within the range O:O and show in cell C20.

I hope I havent been too confusing, I tried to give as much info as possible.

Would really appreciate your help, even if I have to change my format, I am in your hands!!!

[micope21]

Hi

See the file!

Change Student ID number Orange highlight!!

Yellow highlight change automatic

Column S is helper pick up family surname and if any sibling!!

Regard
micope21

[ympiano]

Hi micope21,

Thanks for your assistance. Really appreciate your response and guidance.

Just one question though.

What if there are families with same surnames. Any suggestions. We have a partuicluar surname which is quite common.

Will I need a unique identifier for this?

Sorry, if I have sent this twice!!!

Appreciate your time.

[micope21]

Hi

I have made improvement.

If 2 or more same family surname?,Instead put in Y in Siblings. Put in A.
If other family got other same surname 2 or more put in B, so on follow C to Z next AA to ZZ. If single surname (No family surname) and there other single same surname leave 1 blank put other number in 1.

Look up point to cell is S7

Cell B19

Please Login or Register  to view this content.

This is array formula Ctrl+Shift+Enter. Then copy down

See the file!!

Regard
micope21

[ympiano]

Dear micope21,

I am not sure how to thank you for your patience and advice. It has worked PERFECTLY!!!!

I have been nutting over this for at least 5 months (on my free time) and came to so many dead-ends. I really am thankful.

Problem solved...