PDA

View Full Version : Solved: Help With INDEX-MATCH-SMALL...

Wolfgang
02-05-2007, 06:21 AM
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

xld
02-05-2007, 07:11 AM
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.

xld
02-05-2007, 07:12 AM
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
02-05-2007, 07:23 AM
Hi Bob...

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

Wolfgang

xld
02-05-2007, 07:31 AM
Uh?

Wolfgang
02-05-2007, 07:41 AM
...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