Complicated IF/INDEX/MATCH

EP123

New Member
Joined
Dec 26, 2018
Messages
3
I have what I *think* is just a complicated IF/INDEX/MATCH problem that I have not quite been able to figure out. Hoping someone here can help.

I have two data sets which I am trying to merge. Dataset 1 contains a series of cases defined by a 9-digit serial number, a 4-digit ID number (this is needed as a unique identifier because the same serial # may appear multiple times in the set), a date, and a few columns of additional data. Dataset 2 contains 9-digit serial numbers, dates, and again, a few columns of (different) data. In this set, there are again multiple entries per serial number, but there is no unique identifying 4-digit ID number. The sets overlap completely in that they contain the same 9-digit serial numbers, but there are far more rows/entries in dataset 2.

To properly merge these sets, what I need to do is assign the right 4-digit ID number (along with corresponding date and data) from set 1 to the 9-digit serial numbers in set 2. Usually, for this type of problem, INDEX/MATCH is the solution I employ. However, what makes this a bit more complicated is that there are multiple unique 4-digit ID numbers for many of the 9-digit serial numbers (with the differentiating variable being a date), but simple INDEX/MATCH will only result in assigning one ID number to a given serial number. The right choice of ID number is a function of serial number AND date, and I cannot figure out how to create a formula to incorporate both of these properly.

Below I have provided some example data, including desired output. Basically, I want to match on serial number and merge "pet" data from dataset 1 with dataset 2 provided that the corresponding date for that entry in dataset 1 occurred before the date in for the ID number in dataset 2.

Example Set 1:
[TABLE="class: grid, width: 383"]
<tbody>[TR]
[TD]Serial #[/TD]
[TD]Pet[/TD]
[TD]Date of Pet purchase[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cat[/TD]
[TD="align: right"]3/23/2012[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]dinosaur[/TD]
[TD="align: right"]3/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cow[/TD]
[TD="align: right"]8/24/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]bird[/TD]
[TD="align: right"]9/24/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]frog[/TD]
[TD="align: right"]10/13/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]snake[/TD]
[TD="align: right"]4/6/2012[/TD]
[/TR]
</tbody>[/TABLE]

Example Set 2:
[TABLE="class: grid, width: 356"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[/TR]
</tbody>[/TABLE]

Ideal merged table appearance (though other forms of merge would be ok if this is too difficult - I can make data more 'wide' later):
[TABLE="class: grid, width: 727"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[TD]Pet 1[/TD]
[TD]Pet 2[/TD]
[TD]Pet 3[/TD]
[TD]Pet 4[/TD]
[TD]Pet 5[/TD]
[TD]Pet 6[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD]bird[/TD]
[/TR]
</tbody>[/TABLE]


Any help from experts or ambitious amateurs would be appreciated. Thanks!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Re: Complicated IF/INDEX/MATCH problem (I think...)

How about

Excel 2013/2016
ABCDEFGHIJKLMNOPQR
1Serial #PetDate of Pet purchaseID #Serial #Date of scoreScore
22241081cat23/03/20121098224108121/06/20122Rcatcowfrogsnake
32241081dinosaur13/03/20132056224108106/03/20131Rcatcowfrogsnake
42241081cow24/08/20101115224108118/06/20132Rcatdinosaurcowfrogsnake
52241081bird24/09/20132251224108125/07/20142Rcatdinosaurcowbirdfrogsnake
62241081frog13/10/2010
72241081snake06/04/2012
Sheet6
Cell Formulas
RangeFormula
J2{=IFERROR(INDEX($B$2:$B$7,SMALL(IF(($A$2:$A$7=$G2)*($C$2:$C$7<=$H2),ROW($A$2:$A$7)-ROW($A$2)+1),COLUMNS($A:A))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Re: Complicated IF/INDEX/MATCH problem (I think...)

Worked like a charm. Thank you, Dave2018!


Fluff, apologies for double post. I had a log out/log in issue and wasn't sure post had gone through. I waited 10 minutes, saw no sign of the thread, so re-posted. I will wait a bit longer next time.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top