Forum Discussion
Need some help with my Vlookup or a new idee
Hi Jimmy,
I was not able to convert your text back to Excel sheet from my browser, at least for reasonable time. Here is the sample which illustrates the idea
Entire formula to find Reg No is
=INDEX($C$3:$C$10,MATCH(AGGREGATE(14,6,1/($B$3:$B$10=$F$3)/($G$3>$D$3:$D$10)*$D$3:$D$10,1),$D$3:$D$10,0))
Here AGGREGATE find the largest date in Date column for which Name=a
($B$3:$B$10=$F$3)
and date is less than Born
($G$3>$D$3:$D$10)
You may add here more criteria if needed.
MATCH find position of that date in Date column and INDEX returns Reg No for that position.
Please see attached.
Thank you for a good solution but it works only if there are just one dog with the same birthdate as father/mother dog if there are more than one dog with taht birthdate then it returns the first "REG" number and not the actuale regnr of the dog.
this are my formulas now:
=INDEX($B$2:$B$16088;MATCH(AGGREGATE(14;6;1/($A$2:$A$16088=$D2)/($G2>$G$2:$G$16088)*$G$2:$G$16088;1);$G$2:$G$16088;0))
=INDEX($B$2:$B$16088;MATCH(AGGREGATE(14;6;1/($A$2:$A$16088=$F2)/($G2>$G$2:$G$16088)*$G$2:$G$16088;1);$G$2:$G$16088;0))
But i have scaled it down a little in the excel sheet that i have uploaded here so you can see my dilemma
| Name | Reg_Name | Reg_Father | Father | Reg_Mother | Mother | Born |
| Father one | IS00000/00 | #NUM! | not known | #NUM! | not known | 12.06.1986 |
| mother one | IS00 | #NUM! | not known | #NUM! | not known | 12.06.1987 |
| Father two | IS00000/01 | IS00000/00 | Father one | #NUM! | not known | 24.06.1988 |
| Father two | IS0001/12 | IS00000/00 | Father one | #NUM! | not known | 01.01.2012 |
| Father one | IS0002/12 | IS00000/00 | Father one | IS00 | mother one | 01.01.2012 |
| mother two | IS0001/13 | IS0001/12 | Father two | IS00 | mother one | 01.02.2013 |
| puppy 1 | VIH 155 | IS0001/12 | Father one | IS0001/13 | mother two | 01.04.2015 |
| puppy 2 | VIH 156 | IS0001/12 | Father one | IS0001/13 | mother two | 01.04.2015 |
| puppy 3 | VIH 157 | IS0001/12 | Father one | IS0001/13 | mother two | 01.04.2015 |
| puppy 4 | VIH 158 | IS0001/12 | Father one | IS0001/13 | mother two | 01.04.2015 |
as you see here it actually picks the reg nr for "Father two" even it is the "father one" who is the criteria
Can i solv this in a way? Else the formula works good much better than what i had before.
br
Jimmy
Hi Jim,
I din't took into account you have a lot of puppies born at the same date. Adjusted formula is
=IFERROR(INDEX($B$2:$B$11,MATCH(1,INDEX((AGGREGATE(14,6,1/($A$2:$A$11=$D2)/($G2>$G$2:$G$11)*$G$2:$G$11,1)=$G$2:$G$11)*($D2=$A$2:$A$11),0,1),0)), "no match")
Please see attached
Thank you so much that did the trick and helped me a long way towards the final goal of making a familytree ;)