Solved: Help With INDEX-MATCH-SMALL...

[Wolfgang]

Hi All...

I generated a worksheet which lists and shows upcoming birthdays for each person individually...

All goes well until there are two or more people having the same day and month for their birthdays...

In my example workbook those are marked in red...

I tweaked Fritz and Frank by sheer luck although I have my doubts about that...

Please have a look, thank you...

Best,
Wolfgang

[Bob Phillips]

Wolfgang,

I would add a helper column, in say J with a formuyla of

=E4*10000+YEAR(D4)

copy that donw, then change your formula to

=INDEX($C$4:$C$37,MATCH(SMALL($J$4:$J$37,ROW()-3),$J$4:$J$37,0))&" hat in "&SMALL($E$4:$E$37,ROW()-3)& " Tag(en) Geburtstag !!!"

Of course it still fails if you get more than one born on the same day, same year.

[Bob Phillips]

Should have added that you can get over that with a helper column formula of

=E4*100000+YEAR(D4)*10+COUNTIF($D$1:$D4,D4)-1

[Wolfgang]

Hi Bob...

Thanks for your answer...

When I enter your formula I get a "negative dates will be displayed as ####" error...

Wolfgang

[Bob Phillips]

Uh?

[Wolfgang]

...All OK, BOB...

I don't now how I did it, but I successfully managed to totally screw up my original file beyond recognition...

Your's runs just great and I thank you very much for your time and help...

Best,
Wolfgang