Lookup formula help!

[mrexcelrc]

Hello Excel gurus!
I need help with a lookup formula that will give me the Balance of a TradeID as of a certain Amort_Date based on the table shown below. The tricky part for me is to lookup the balance as of a particular date that is not listed on the table and take the most recent Balance prior to that lookup date. For example, if I want to lookup the Balance of TradeID 50402L as of 20140131 which is not listed in the table, I would want the formula to return a value of 28250000 which is the Balance as of 20140102 which is the most recent Balance prior to 20140131.

Assume this data is on cells A1..C10 but my actual data set is much larger than this example. Can anyone of you fine Excel masters help me?
Very much appreciate your help. Thank you.

TradeID	Amort_Date	Balance
50292L	20140102	20137350
50292L	20140203	20116475
50292L	20140301	20109989
50402L	20140102	28250000
50402L	20140201	28238570
50402L	20140302	27956050
50478L	20140101	28400000
50478L	20140201	27894575
50478L	20140301	26948656

<tbody>
</tbody>

 

[iyyi]

if your TradeID is in E2 and the Amort_Date is in F2 then
Try: =INDEX(C2:C7,MATCH(E2&F2,A2:A7&B2:B7))
Enter with Ctrl+Shift+Enter

 

[Shrikant]

iyyi's solution is better.


If your data is sorted in first 2 columns then try this.


Yellow color cells are input cells.

https://www.dropbox.com/s/5kj5gifupm44kty/mrexcelrc.xlsb

 

[Aladin Akyurek]

TradeID	Amort_Date	Balance
50292L	20140102	20137350		50402L	20140131	28250000
50292L	20140203	20116475
50292L	20140301	20109989
50402L	20140102	28250000
50402L	20140201	28238570
50402L	20140302	27956050
50478L	20140101	28400000
50478L	20140201	27894575
50478L	20140301	26948656

<COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" width=98><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 967" width=27><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3128" width=88><TBODY>
</TBODY>

E2: 50402L

F2: 20140131

G2:

1. If the dates are in ascending order...

Control+shift+enter, not just enter:

=INDEX($C$2:$C$10,MATCH($F2,IF($A$2:$A$10=$E2,$B$2:$B$10),1))

2. Otherwise...

Control+shift+enter, not just enter:

=INDEX($C$2:$C$10,MATCH(MAX(IF($A$2:$A$10=$E2,
  IF($B$2:$B$10<=$F2,$B$2:$B$10))),IF($A$2:$A$10=$E2,
  IF($B$2:$B$10<=F2,$B$2:$B$10)),0))

 

[mrexcelrc]

Thank you Iyyi. It worked perfectly.