Anyone have a good solution to this problem?

Status
Not open for further replies.

Jimmy_No

New Member
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 :
 Name Reg_Name Reg_Father Father Reg_Mother Mother Born Father one IS00000/00 #N/A not known #N/A not known 12.06.1986 Mother one IS00 #N/A not known #N/A not known 12.06.1987 Father two IS00000/01 IS00000/00 Father one #N/A not known 24.06.1988 Father one IS0001/12 IS00000/00 Father one IS00 mother one 01.01.2012 Mother two IS0001/13 IS00000/01 Father two IS00 mother one 01.02.2013 puppy 1 VIH 155 IS00000/00 Father one IS0001/13 mother two 01.04.2015 puppy 2 VIH 156 IS00000/00 Father one IS0001/13 mother two 01.04.2015 puppy 3 VIH 157 IS00000/00 Father one IS0001/13 mother two 01.04.2015

<tbody>
</tbody>

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?

What i want is to find the "Reg_Father" and "Reg_mother" based on their 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 their forfathers then Vlookup picks the first occurrence of that name. I want it to pick the last occurrence 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

br
Jimmy

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Status
Not open for further replies.

Replies
3
Views
338

1,136,326
Messages
5,675,115
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back