Match Between Criteria

[jfish1288]

I have data in the following form:

<table border="0" cellpadding="0" cellspacing="0" width="320"><colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">A</td> <td style="width:48pt" width="64">B</td> <td style="width:48pt" width="64">C</td> <td style="width:48pt" width="64">D</td> <td style="width:48pt" width="64">E</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Part Type</td> <td>Date Ordered</td> <td># Ordered</td> <td>Starting Inventory #</td> <td>Ending Inventory #</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">54BRD</td> <td class="xl24" align="right">4/5/2011</td> <td align="right">192</td> <td align="right">821226</td> <td align="right">821417</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">54AHS</td> <td class="xl24" align="right">6/28/2011</td> <td align="right">960</td> <td align="right">461858</td> <td align="right">462817</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">13AOS</td> <td class="xl24" align="right">7/15/2011</td> <td align="right">150</td> <td align="right">568545</td> <td align="right">568694</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">84AOG</td> <td class="xl24" align="right">8/1/2011</td> <td align="right">160</td> <td align="right">718216</td> <td align="right">718375</td> </tr> </tbody></table>
I need a formula to find the Date Ordered, given an inventory number. So if I entered 821300 in cell G2, I would want 4/5/2011 returned in H2. 568600 would return 7/15/11.

 

[Matty]

Try:

=INDEX(B2:B5,MATCH(1,IF(G2>=D2:D5,IF(G2<=E2:E5,1)),0))

Array entered, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[jfish1288]

Thanks

 

[Matty]

Thanks

No problem. Glad it worked.

Matty