Index and Match Help

[Mr.Big Head]

I have a slight problem I need help with. I have two data sets that I am trying to cross reference and extract information from. My column are as such

A: Number B:Price C: Effective Date D: Termination Date
123 $1 20111226 20121231


Those four columns are my master data set. The goal is to extract the price from column B with and place it in the appropriate corresponding place.

Column I: Number J: Date of Service L: Price
123 20120501 ???

The problem that I am having is I can't create a formula that will look at the date of service and compare it against columns C and D and if it falls between those values return to me column B, if columns I and A are a match.

Anybody have any thoughts?

 

[Gerald Higgins]

Hi, welcome to the board.

This looks like it might be straightforward, but I need to check.
I'm guessing you actually have several rows, like the first one.
Do the dates ever overlap for the same Number ?
And is the data sorted by number first ?
If yes, then this should all be do-able.

Just to check, please can you post a slightly larger sample of your data, covering at least two "numbers", and several date ranges for each number.

 

[Mr.Big Head]

Hi, welcome to the board.

This looks like it might be straightforward, but I need to check.
I'm guessing you actually have several rows, like the first one.
Do the dates ever overlap for the same Number ?
And is the data sorted by number first ?
If yes, then this should all be do-able.

Just to check, please can you post a slightly larger sample of your data, covering at least two "numbers", and several date ranges for each number.

You are correct, there are several rows that are similar to the first one. For every number the date range is unique and will never have an overlapping date. The data is sorted by Column A from lowest to highest.

<table style="width: 457px; height: 630px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:63pt" height="20" width="84">Number</td> <td style="width:47pt" width="63">Price</td> <td style="width:71pt" width="95">Effective Date</td> <td style="width:87pt" width="116">Termination Date</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">1</td> <td align="right">2011122 9</td> <td class="xl64">99991231</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">2</td> <td align="right">20110324
</td> <td class="xl64"> 20111228</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">3</td> <td align="right">20100701</td> <td class="xl64"> 20110323</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">4</td> <td align="right">20100105</td> <td class="xl64"> 20100630</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">5</td> <td align="right">20090115</td> <td class="xl64"> 20100104</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">6</td> <td align="right">20080701</td> <td class="xl64"> 20090114</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">7</td> <td align="right">20070627</td> <td class="xl64"> 20080630</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">8</td> <td align="right">20060615</td> <td class="xl64"> 20070626</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">9</td> <td align="right">20050331</td> <td class="xl64"> 20060614</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">10</td> <td align="right">20040520</td> <td class="xl64"> 20050330</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">11</td> <td align="right">20030916</td> <td class="xl64"> 20040519</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">123</td> <td align="right">12</td> <td align="right">20030212</td> <td class="xl64"> 20030915</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">456</td> <td align="right">100</td> <td align="right">20020703</td> <td class="xl64"> 99991231</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">456</td> <td align="right">101</td> <td align="right">20020110</td> <td class="xl64"> 20020702</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">456</td> <td align="right">102</td> <td align="right">20011031</td> <td class="xl64"> 20020109</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1000</td> <td align="right">20111229</td> <td class="xl64"> 99991231</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1001</td> <td align="right">20110324</td> <td class="xl64"> 20111228</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1002</td> <td align="right">20100302</td> <td class="xl64"> 20110323</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1003</td> <td align="right">20090617</td> <td class="xl64"> 20100301</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1004</td> <td align="right">20081111</td> <td class="xl64"> 20090616</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1005</td> <td align="right">20080507</td> <td class="xl64"> 20081110</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1006</td> <td align="right">20070612</td> <td class="xl64"> 20080506</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1007</td> <td align="right">20060810</td> <td class="xl64"> 20070611</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1008</td> <td align="right">20060214</td> <td class="xl64"> 20060809</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1009</td> <td align="right">20050907</td> <td class="xl64"> 20060213</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1010</td> <td align="right">20050427</td> <td class="xl64"> 20050906</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1011</td> <td align="right">20040701</td> <td class="xl64"> 20050426</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1012</td> <td align="right">20030916</td> <td class="xl64"> 20040630</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">789</td> <td align="right">1013</td> <td align="right">20021216</td> <td class="xl64"> 20030915</td> </tr> </tbody></table>

 

[Mr.Big Head]

The Date range is screw up and wouldn't let me post it cleanly, but the date format is YYYYMMDD

 

[Gerald Higgins]

OK this is do-able. I have to go offline now, but I'll look back tonight (UK time). Maybe someone else can post a solution in the meantime, if not I'll do it tonight.

 

[Mr.Big Head]

Any update on this?