Vlookup or other function within excel

[Jimmy_No]

Hi
I have a table of 16087 rows and 7 columns. and im strugling with similer names in column A and Vlookup that picks the first hit.
This is a dogbreed that have dogs with simple names and i want to pick right father/mother combinations based on age and probability to puppies.
Now i have these lookups in place
=VLOOKUP(D2;$A$2:$B$9;2;FALSE) "Father"
=VLOOKUP(F2;$A$2:$B$9;2;FALSE) "mother"
I have there names but i like to also add Reg nr
my table looks like this and are sorted on "Born" the date the puppies where born :
[TABLE="width: 1050"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Reg_Name[/TD]
[TD]Reg_Father[/TD]
[TD]Father[/TD]
[TD]Reg_Mother[/TD]
[TD]Mother[/TD]
[TD]Born[/TD]
[/TR]
[TR]
[TD]Father one[/TD]
[TD]IS00000/00[/TD]
[TD="align: center"]#N/A[/TD]
[TD]not known[/TD]
[TD="align: center"]#N/A[/TD]
[TD]not known[/TD]
[TD="align: right"]12.06.1986[/TD]
[/TR]
[TR]
[TD]Mother one[/TD]
[TD]IS00[/TD]
[TD="align: center"]#N/A[/TD]
[TD]not known[/TD]
[TD="align: center"]#N/A[/TD]
[TD]not known[/TD]
[TD="align: right"]12.06.1987[/TD]
[/TR]
[TR]
[TD]Father two[/TD]
[TD]IS00000/01[/TD]
[TD]IS00000/00[/TD]
[TD]Father one[/TD]
[TD="align: center"]#N/A[/TD]
[TD]not known[/TD]
[TD="align: right"]24.06.1988[/TD]
[/TR]
[TR]
[TD]Father one[/TD]
[TD]IS0001/12[/TD]
[TD]IS00000/00[/TD]
[TD]Father one[/TD]
[TD]IS00[/TD]
[TD]mother one[/TD]
[TD="align: right"]01.01.2012[/TD]
[/TR]
[TR]
[TD]Mother two[/TD]
[TD]IS0001/13[/TD]
[TD]IS00000/01[/TD]
[TD]Father two[/TD]
[TD]IS00[/TD]
[TD]mother one[/TD]
[TD="align: right"]01.02.2013[/TD]
[/TR]
[TR]
[TD]puppy 1[/TD]
[TD]VIH 155[/TD]
[TD]IS00000/00[/TD]
[TD]Father one[/TD]
[TD]IS0001/13[/TD]
[TD]mother two[/TD]
[TD="align: right"]01.04.2015[/TD]
[/TR]
[TR]
[TD]puppy 2[/TD]
[TD]VIH 156[/TD]
[TD]IS00000/00[/TD]
[TD]Father one[/TD]
[TD]IS0001/13[/TD]
[TD]mother two[/TD]
[TD="align: right"]01.04.2015[/TD]
[/TR]
[TR]
[TD]puppy 3[/TD]
[TD]VIH 157[/TD]
[TD]IS00000/00[/TD]
[TD]Father one[/TD]
[TD]IS0001/13[/TD]
[TD]mother two[/TD]
[TD="align: right"]01.04.2015[/TD]
[/TR]
</tbody>[/TABLE]

what i want is to pick the right "father_one" with the age nearest to the puppies but still older than the puppies (and with a age differanse less than 14 years had been nice to have)

Is there any functions i can combine to get this to work?

hope someone has any idee so i can get past this problem

br
Jimmy

 

[crazydragon84]

let me get this right, your "inputs" are Name and date of birth. You want to look this name up on "NAME" column, where the date is GREATER than the date of birth, and return the value in REG_NAME. Is that correct?

Here is what you can do
1. you need to change the date format to actual dates I think. (Just select column F, and Find and replace all periods with /)
2. use this formula: =vlookup(Name,indirect("A" & countif(F:F,"<" & birth_Date)+ 2 & ":B16087"),2,0)

The indirect is looking at column F and counting how many dates there are less than the birthday, and adding 2 rows (one for header and one to go to the next row, which is the first row that will have greater date than your selected birthday). Let me know if that helps.

 

[Jimmy_No]

What i want is to find the "Reg_Father" and "Reg_mother" based on there name, this are values that are in the Reg_name column before the puppies are born, the problem is when at father or mother has the same name as one of there forfathers then Vlookup picks the first accurance of that name. I want it to pick the last accurance before the puppies was born not the first and not the last because it may appear later in the list aswell.
so to sum it up i want:
- mother and father reg nr
- closest to puppies born date
- but not after they have been born
- based on mother/fathers name

 

[Jimmy_No]

I cant seem to get this to work