Anyone have a good solution to this problem?

Status
Not open for further replies.

Jimmy_No

New Member
Joined
Apr 23, 2018
Messages
4
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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top