lookup formula or other

[jcaptchaos2]

I have this table in E3:G15 I need a formula for J4 to look at I4 and bring back column 'G' for the answer. I know how to do Vlook up's but I would have to have all of the numbers in my table. I need the formula to look at column 'I' and the look at coulumns 'E'&'F' and bring back 'G'. for example in the table I posed below my answer in 'J4" would be 1.

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0">E</td><td align="center" bgcolor="#C0C0C0">F</td><td align="center" bgcolor="#C0C0C0">G</td><td align="center" bgcolor="#C0C0C0">H</td><td align="center" bgcolor="#C0C0C0">I</td><td align="center" bgcolor="#C0C0C0">J</td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">3</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Start </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">End </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Month </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Date </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Month </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">4</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070101 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070204 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">1 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070120 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">5</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070205 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070304 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">6</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070305 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070401 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">3 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">7</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070402 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070506 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">4 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">8</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070507 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070603 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">5 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">9</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070604 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070630 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">6 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">10</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070701 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070805 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">7 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">11</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070806 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070831 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">8 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">12</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070901 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20070930 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">9 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">13</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071001 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071102 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">10 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">14</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071103 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071130 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">11 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">15</td><td rowspan="1" colspan="1" width="130.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071201 </font></td><td rowspan="1" colspan="1" width="112.5" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20071231 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "center" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">12 </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="96" height="25.5" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
</table>

 

[Seti]

How about =INDEX(G4:G15,MATCH(I4,E4:E15,1))

 

[jcaptchaos2]

Cool, Thanks Seti, I was thinking I was going to have to put all the dates in a table and use VLOOKUP, this works great Thanks again.

 

[barry houdini]

Perhaps also

=LOOKUP(I4,E4:G15)

 

[jcaptchaos2]

Cool, thanks again both work just as I needed.