SOLVED
Home

Need some help with my Vlookup or a new idee

%3CLINGO-SUB%20id%3D%22lingo-sub-187823%22%20slang%3D%22en-US%22%3ENeed%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20a%20table%20of%2016087%20rows%20and%207%20columns.%20and%20im%20strugling%20with%20similer%20names%20in%20column%20A%20and%20Vlookup%20that%20picks%20the%20first%20hit.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThis%20is%20a%20dogbreed%20that%20have%20dogs%20with%20simple%20names%20and%20i%20want%20to%20pick%20right%20father%2Fmother%20combinations%20based%20on%20age%20and%20probability%20to%20puppies.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ENow%20i%20have%20these%20lookups%20in%20place%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%3DVLOOKUP(D2%3B%24A%242%3A%24B%249%3B2%3BFALSE)%20%22Father%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%3DVLOOKUP(F2%3B%24A%242%3A%24B%249%3B2%3BFALSE)%20%22mother%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20there%20names%20but%20i%20like%20to%20also%20add%20Reg%20nr%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Emy%20table%20looks%20like%20this%20and%20are%20sorted%20on%20%22Born%22%20the%20date%20the%20puppies%20where%20born%20%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22cms_table%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EReg_Name%3C%2FTD%3E%3CTD%3EReg_Father%3C%2FTD%3E%3CTD%3EFather%3C%2FTD%3E%3CTD%3EReg_Mother%3C%2FTD%3E%3CTD%3EMother%3C%2FTD%3E%3CTD%3EBorn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E12.06.1986%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMother%20one%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E12.06.1987%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20two%3C%2FTD%3E%3CTD%3EIS00000%2F01%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E24.06.1988%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3Emother%20one%3C%2FTD%3E%3CTD%3E01.01.2012%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMother%20two%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3EIS00000%2F01%3C%2FTD%3E%3CTD%3EFather%20two%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3Emother%20one%3C%2FTD%3E%3CTD%3E01.02.2013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%201%3C%2FTD%3E%3CTD%3EVIH%20155%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%202%3C%2FTD%3E%3CTD%3EVIH%20156%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%203%3C%2FTD%3E%3CTD%3EVIH%20157%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3Ewhat%20i%20want%20is%20to%20pick%20the%20right%20%22father_one%22%20with%20the%20age%20nearest%20to%20the%20puppies%20but%20still%20older%20than%20the%20puppies%20(and%20with%20a%20age%20differanse%20less%20than%2014%20years%20had%20been%20nice%20to%20have)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIs%20there%20any%20functions%20i%20can%20combine%20to%20get%20this%20to%20work%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWhat%20i%20want%20is%20to%20find%20the%20%22Reg_Father%22%20and%20%22Reg_mother%22%20based%20on%20their%20name%2C%20this%20are%20values%20that%20are%20in%20the%20Reg_name%20column%20before%20the%20puppies%20are%20born%2C%20the%20problem%20is%20when%20at%20father%20or%20mother%20has%20the%20same%20name%20as%20one%20of%20their%20forfathers%20then%20Vlookup%20picks%20the%20first%20occurrence%20of%20that%20name.%20I%20want%20it%20to%20pick%20the%20last%20occurrence%20before%20the%20puppies%20was%20born%20not%20the%20first%20and%20not%20the%20last%20because%20it%20may%20appear%20later%20in%20the%20list%20aswell.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Eso%20to%20sum%20it%20up%20i%20want%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20mother%20and%20father%20reg%20nr%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20closest%20to%20puppies%20born%20date%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20but%20not%20after%20they%20have%20been%20born%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20based%20on%20mother%2Ffathers%20name%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Ebr%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EJimmy%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-187823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-192210%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-192210%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20understand%20your%20issue%20correctly%2C%20you%20have%20an%20issue%20with%20the%20VLOOKUP%20Function%20discriminating%20between%20duplicate%20or%20similar%20names.%20Try%20making%20a%20new%20column%20an%20Concatenate%20the%20Name%20and%20the%20Date%20in%20question.%20Something%20like%26nbsp%3B%20%22Smokey_1965%22%20and%20then%20use%20the%20VLOOKUP%20Function%20on%20the%20new%20column.%20Hope%20this%20works%20%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188410%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188410%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20that%20did%20the%20trick%20and%20helped%20me%20a%20long%20way%20towards%20the%20final%20goal%20of%20making%20a%20familytree%20%3B)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188407%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188407%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jim%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20din't%20took%20into%20account%20you%20have%20a%20lot%20of%20puppies%20born%20at%20the%20same%20date.%20Adjusted%20formula%20is%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24B%242%3A%24B%2411%2CMATCH(1%2CINDEX((AGGREGATE(14%2C6%2C1%2F(%24A%242%3A%24A%2411%3D%24D2)%2F(%24G2%26gt%3B%24G%242%3A%24G%2411)*%24G%242%3A%24G%2411%2C1)%3D%24G%242%3A%24G%2411)*(%24D2%3D%24A%242%3A%24A%2411)%2C0%2C1)%2C0))%2C%20%22no%20match%22)%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188288%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188288%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20a%20good%20solution%20but%20it%20works%20only%20if%20there%20are%20just%20one%20dog%20with%20the%20same%20birthdate%20as%20father%2Fmother%20dog%20if%20there%20are%20more%20than%20one%20dog%20with%20taht%20birthdate%20then%20it%20returns%20the%20first%20%22REG%22%20number%20and%20not%20the%20actuale%20regnr%20of%20the%20dog.%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20are%20my%20formulas%20now%3A%3C%2FP%3E%3CP%3E%3DINDEX(%24B%242%3A%24B%2416088%3BMATCH(AGGREGATE(14%3B6%3B1%2F(%24A%242%3A%24A%2416088%3D%24D2)%2F(%24G2%26gt%3B%24G%242%3A%24G%2416088)*%24G%242%3A%24G%2416088%3B1)%3B%24G%242%3A%24G%2416088%3B0))%3CBR%20%2F%3E%3DINDEX(%24B%242%3A%24B%2416088%3BMATCH(AGGREGATE(14%3B6%3B1%2F(%24A%242%3A%24A%2416088%3D%24F2)%2F(%24G2%26gt%3B%24G%242%3A%24G%2416088)*%24G%242%3A%24G%2416088%3B1)%3B%24G%242%3A%24G%2416088%3B0))%3C%2FP%3E%3CP%3EBut%20i%20have%20scaled%20it%20down%20a%20little%20in%20the%20excel%20sheet%20that%20i%20have%20uploaded%20here%20so%20you%20can%20see%20my%20dilemma%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EReg_Name%3C%2FTD%3E%3CTD%3EReg_Father%3C%2FTD%3E%3CTD%3EFather%3C%2FTD%3E%3CTD%3EReg_Mother%3C%2FTD%3E%3CTD%3EMother%3C%2FTD%3E%3CTD%3EBorn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E12.06.1986%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Emother%20one%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E12.06.1987%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20two%3C%2FTD%3E%3CTD%3EIS00000%2F01%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E24.06.1988%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20two%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3E%23NUM!%3C%2FTD%3E%3CTD%3Enot%20known%3C%2FTD%3E%3CTD%3E01.01.2012%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0002%2F12%3C%2FTD%3E%3CTD%3EIS00000%2F00%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3Emother%20one%3C%2FTD%3E%3CTD%3E01.01.2012%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EFather%20two%3C%2FTD%3E%3CTD%3EIS00%3C%2FTD%3E%3CTD%3Emother%20one%3C%2FTD%3E%3CTD%3E01.02.2013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%201%3C%2FTD%3E%3CTD%3EVIH%20155%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%202%3C%2FTD%3E%3CTD%3EVIH%20156%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%203%3C%2FTD%3E%3CTD%3EVIH%20157%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epuppy%204%3C%2FTD%3E%3CTD%3EVIH%20158%3C%2FTD%3E%3CTD%3EIS0001%2F12%3C%2FTD%3E%3CTD%3EFather%20one%3C%2FTD%3E%3CTD%3EIS0001%2F13%3C%2FTD%3E%3CTD%3Emother%20two%3C%2FTD%3E%3CTD%3E01.04.2015%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eas%20you%20see%20here%20it%20actually%20picks%20the%20reg%20nr%20for%20%22Father%20two%22%20even%20it%20is%20the%20%22father%20one%22%20who%20is%20the%20criteria%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20i%20solv%20this%20in%20a%20way%3F%20Else%20the%20formula%20works%20good%20much%20better%20than%20what%20i%20had%20before.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebr%26nbsp%3B%3C%2FP%3E%3CP%3EJimmy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187993%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20my%20Vlookup%20or%20a%20new%20idee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187993%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jimmy%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20was%20not%20able%20to%20convert%20your%20text%20back%20to%20Excel%20sheet%20from%20my%20browser%2C%20at%20least%20for%20reasonable%20time.%20Here%20is%20the%20sample%20which%20illustrates%20the%20idea%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F33135i788453BE45AB1D3C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EEntire%20formula%20to%20find%20Reg%20No%20is%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24C%243%3A%24C%2410%2CMATCH(AGGREGATE(14%2C6%2C1%2F(%24B%243%3A%24B%2410%3D%24F%243)%2F(%24G%243%26gt%3B%24D%243%3A%24D%2410)*%24D%243%3A%24D%2410%2C1)%2C%24D%243%3A%24D%2410%2C0))%3C%2FPRE%3E%0A%3CP%3EHere%20AGGREGATE%20find%20the%20largest%20date%20in%20Date%20column%20for%20which%20Name%3Da%3C%2FP%3E%0A%3CPRE%3E(%24B%243%3A%24B%2410%3D%24F%243)%3C%2FPRE%3E%0A%3CP%3Eand%20date%20is%20less%20than%20Born%3C%2FP%3E%0A%3CPRE%3E(%24G%243%26gt%3B%24D%243%3A%24D%2410)%3C%2FPRE%3E%0A%3CP%3EYou%20may%20add%20here%20more%20criteria%20if%20needed.%3C%2FP%3E%0A%3CP%3EMATCH%20find%20position%20of%20that%20date%20in%20Date%20column%20and%20INDEX%20returns%20Reg%20No%20for%20that%20position.%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 :

NameReg_NameReg_FatherFatherReg_MotherMotherBorn
Father oneIS00000/00#N/Anot known#N/Anot known12.06.1986
Mother oneIS00#N/Anot known#N/Anot known12.06.1987
Father twoIS00000/01IS00000/00Father one#N/Anot known24.06.1988
Father oneIS0001/12IS00000/00Father oneIS00mother one01.01.2012
Mother twoIS0001/13IS00000/01Father twoIS00mother one01.02.2013
puppy 1VIH 155IS00000/00Father oneIS0001/13mother two01.04.2015
puppy 2VIH 156IS00000/00Father oneIS0001/13mother two01.04.2015
puppy 3VIH 157IS00000/00Father oneIS0001/13mother two01.04.2015


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

5 Replies
Highlighted
Solution

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

image.png

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.

Highlighted

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 

NameReg_NameReg_FatherFatherReg_MotherMotherBorn
Father oneIS00000/00#NUM!not known#NUM!not known12.06.1986
mother oneIS00#NUM!not known#NUM!not known12.06.1987
Father twoIS00000/01IS00000/00Father one#NUM!not known24.06.1988
Father twoIS0001/12IS00000/00Father one#NUM!not known01.01.2012
Father oneIS0002/12IS00000/00Father oneIS00mother one01.01.2012
mother twoIS0001/13IS0001/12Father twoIS00mother one01.02.2013
puppy 1VIH 155IS0001/12Father oneIS0001/13mother two01.04.2015
puppy 2VIH 156IS0001/12Father oneIS0001/13mother two01.04.2015
puppy 3VIH 157IS0001/12Father oneIS0001/13mother two01.04.2015
puppy 4VIH 158IS0001/12Father oneIS0001/13mother two01.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

Highlighted

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

Highlighted

Thank you so much that did the trick and helped me a long way towards the final goal of making a familytree ;) 

 

Highlighted

If I understand your issue correctly, you have an issue with the VLOOKUP Function discriminating between duplicate or similar names. Try making a new column an Concatenate the Name and the Date in question. Something like  "Smokey_1965" and then use the VLOOKUP Function on the new column. Hope this works :-)