Multiple sheet and multiple value INDEX MATCH that includes a date range return

TBahr

New Member
Joined
Mar 9, 2016
Messages
2
I've been looking through the forum for an answer, and although I've seen some very similar posts, I can't get a definitive answer for my issue: Here is an example of what I am working with and what I'm trying for my INDEX MATCH


=INDEX(Sheet1!A1:A1312,MATCH(Sheet2!$A$1:$A$9999&$B$1:$B$9999,Sheet1!$A$1:$A$9999&(Sheet2!$B$1>=Sheet1!$B$1)*(Sheet2!$A$1<=Sheet1!$C$1),0))

What I am trying to create is are two very similar INDEX MATCHES in Sheet 1: Column C would show the Beginning Date (Sheet 2, column B) and Column D the Ending Date (Sheet 2 Column C) that the incident date occurs in if the SIZE CODE (BIG-01, etc) is the same. My searching suggests that the INDEX/MATCH is the simplest means to accomplish this, but if VBA or some other method is better suited, I would appreciate any input as I am relatively new to this sort of Excel power-usage.

Thanks for considering!


Sheet 1

A B

BIG-01 7/5/2012
BIG-01 1/16/2010
BIG-01 4/18/2011
BIG-01 9/11/2013
BIG-01 6/6/2009
BIG-01 8/11/2012
BIG-01 10/10/2012
BIG-01 12/15/2014
LITTLE-01 7/5/2012
LITTLE-01 1/16/2010
LITTLE-01 4/18/2011
LITTLE-01 9/11/2013
LITTLE-01 6/6/2009
LITTLE-01 8/11/2012
LITTLE-01 10/10/2012
LITTLE-01 12/15/2014
LITTLE-01 3/15/2008
MEDIUM-01 4/25/2015
MEDIUM-01 7/5/2012
MEDIUM-01 1/16/2010
MEDIUM-01 4/18/2011
MEDIUM-01 9/11/2013
MEDIUM-01 6/6/2009
MEDIUM-01 8/11/2012
MEDIUM-01 10/10/2012
MEDIUM-01 12/15/2014

Sheet 1
A B C
BIG-01 11/1/2012 11/1/2013
BIG-01 11/1/2013 11/1/2014
BIG-01 11/1/2014 11/1/2015
BIG-01 11/1/2015 11/1/2016
BIG-01 11/1/2011 11/1/2012
BIG-01 11/1/2010 11/1/2011
BIG-01 11/1/2009 11/1/2010
BIG-01 11/1/2008 11/1/2009
BIG-01 11/1/2007 11/1/2008
LITTLE-01 11/1/2012 11/1/2013
LITTLE-01 11/1/2013 11/1/2014
LITTLE-01 11/1/2014 11/1/2015
LITTLE-01 11/1/2015 11/1/2016
LITTLE-01 11/1/2011 11/1/2012
LITTLE-01 11/1/2010 11/1/2011
LITTLE-01 11/1/2009 11/1/2010
LITTLE-01 11/1/2008 11/1/2009
LITTLE-01 11/1/2007 11/1/2008
MEDIUM-01 11/1/2012 11/1/2013
MEDIUM-01 11/1/2013 11/1/2014
MEDIUM-01 11/1/2014 11/1/2015
MEDIUM-01 11/1/2015 11/1/2016
MEDIUM-01 11/1/2011 11/1/2012
MEDIUM-01 11/1/2010 11/1/2011
MEDIUM-01 11/1/2009 11/1/2010
MEDIUM-01 11/1/2008 11/1/2009
MEDIUM-01 11/1/2007 11/1/2008
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

TBahr

New Member
Joined
Mar 9, 2016
Messages
2
I'm still researching, and haven't yet found a solution. If anyone has insight, I would be eternally grateful.
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Hi TBahr,

Can you use something like this? If I understand you correctly, do you want to link the incident to year interval?
Mike Szczesny
<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Company</td><td style=";">Incident Date</td><td style=";">Year</td><td style="text-align: right;;"></td><td style=";">Year</td><td style=";">Beg. Date</td><td style=";">End. Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;">07/05/12</td><td style=";">a5</td><td style="text-align: right;;"></td><td style=";">a1</td><td style="text-align: right;;">11/01/12</td><td style="text-align: right;;">11/01/13</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;">01/16/10</td><td style=";">a7</td><td style="text-align: right;;"></td><td style=";">a2</td><td style="text-align: right;;">11/01/13</td><td style="text-align: right;;">11/01/14</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;">04/18/11</td><td style=";">a6</td><td style="text-align: right;;"></td><td style=";">a3</td><td style="text-align: right;;">11/01/14</td><td style="text-align: right;;">11/01/15</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">09/01/13</td><td style=";">a1</td><td style="text-align: right;;"></td><td style=";">a4</td><td style="text-align: right;;">11/01/15</td><td style="text-align: right;;">11/01/16</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">06/06/09</td><td style=";">a8</td><td style="text-align: right;;"></td><td style=";">a5</td><td style="text-align: right;;">11/01/11</td><td style="text-align: right;;">11/01/12</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">A</td><td style="text-align: right;;">08/11/12</td><td style=";">a5</td><td style="text-align: right;;"></td><td style=";">a6</td><td style="text-align: right;;">11/01/10</td><td style="text-align: right;;">11/01/11</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;">10/10/12</td><td style=";">a5</td><td style="text-align: right;;"></td><td style=";">a7</td><td style="text-align: right;;">11/01/09</td><td style="text-align: right;;">11/01/10</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A</td><td style="text-align: right;;">12/15/14</td><td style=";">a3</td><td style="text-align: right;;"></td><td style=";">a8</td><td style="text-align: right;;">11/01/08</td><td style="text-align: right;;">11/01/09</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a9</td><td style="text-align: right;;">11/01/07</td><td style="text-align: right;;">11/01/08</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B2>=$F$2:$F$10</font>)*(<font color="Green">B2<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B3>=$F$2:$F$10</font>)*(<font color="Green">B3<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B4>=$F$2:$F$10</font>)*(<font color="Green">B4<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B5>=$F$2:$F$10</font>)*(<font color="Green">B5<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B6>=$F$2:$F$10</font>)*(<font color="Green">B6<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C7</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B7>=$F$2:$F$10</font>)*(<font color="Green">B7<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C8</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B8>=$F$2:$F$10</font>)*(<font color="Green">B8<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C9</th><td style="text-align:left">{=LOOKUP(<font color="Blue">2,1/(<font color="Red">(<font color="Green">B9>=$F$2:$F$10</font>)*(<font color="Green">B9<=$G$2:$G$10</font>)</font>),$E$2:$E$10</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Forum statistics

Threads
1,141,204
Messages
5,704,952
Members
421,372
Latest member
Jamie11

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