Vlookup or Index Match not working

[deverhart]

I am having some consistently inconsistent results using either vlookup or index match. i am looking up interest rates in a table to find the market strike price when i open the workbook. The user will select a product from the drop down and based on the product you will get a title in Cell B11 "Base Rate" or "Coupon" which is just a simple vlookup as well using the product list and the verbiage desired:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product List[/TD]
[TD]Rate/Coupon[/TD]
[TD]Hedge Category[/TD]
[/TR]
[TR]
[TD]CON 30[/TD]
[TD]Base Rate[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CON 20[/TD]
[TD]Base Rate[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]VA 30[/TD]
[TD]Coupon[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]VA 15[/TD]
[TD]Coupon[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

This is the "Product Index" Tab. On the main "Trade Calc" tab they input either the Base Rate or the Coupon they desire. If they choose Base Rate then they get a 5 rate range and if they choose coupon they get a 4 rate range that populate on the "Trade Calc" tab and are under the Note Rate Column which are calculated as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Note Rate[/TD]
[TD]Equation[/TD]
[/TR]
[TR]
[TD]3.125%[/TD]
[TD]=IF($B$11="Base Rate",($C$11-0.0025),VLOOKUP($C$11,MarkToMarket!$S$86:$T$109,2,FALSE))[/TD]
[/TR]
[TR]
[TD]3.250%[/TD]
[TD]=IF($B$11="Base Rate",($C$11-0.00125),(VLOOKUP($C$11,MarkToMarket!$S$86:$T$109,2,FALSE)+0.00125))[/TD]
[/TR]
[TR]
[TD]3.375%[/TD]
[TD]=IF($B$11="Base Rate",$C$11,(VLOOKUP($C$11,MarkToMarket!$S$86:$T$109,2,FALSE)+0.0025))[/TD]
[/TR]
[TR]
[TD]3.500%[/TD]
[TD]=IF($B$11="Base Rate",$C$11+0.00125,(VLOOKUP($C$11,MarkToMarket!$S$86:$T$109,2,FALSE)+0.00375))[/TD]
[/TR]
[TR]
[TD]3.625%[/TD]
[TD]=IF($B$11="Base Rate",$C$11+0.0025," ")[/TD]
[/TR]
</tbody>[/TABLE]

this all works as intended but in trying to get the market price i am using the following equation:=IF(VLOOKUP($C$10,'Product Index'!$A$3:$C$16,3,FALSE)=1,INDEX(MarkToMarket!$F$3:$F$28,MATCH(C16,MarkToMarket!$A$3:$A$28,FALSE)),IF(VLOOKUP($C$10,'Product Index'!$A$3:$C$16,3,FALSE)=2,INDEX(MarkToMarket!$F$31:$F$56,MATCH('Trade Calc.'!C16,MarkToMarket!$A$31:$A$56,FALSE)),IF(VLOOKUP($C$10,'Product Index'!$A$3:$C$16,3,FALSE)=3,INDEX(MarkToMarket!$O$86:$O$109,MATCH('Trade Calc.'!C16,MarkToMarket!$J$86:$J$109,FALSE),0))))

I have also tried using a vlookup in the same INDEX MATCH area and i get the same results. Based on the chart above showing the Note Rates i will call 3.125% box 1, 3.250% box 2, ETC.... when the rate is input in cell C11 on the "Trade Calc" tab the "#N/A" happens consistently for every 6th input. If C11 is 3.375/4.125/4.875 then the error is in box 5 (adding .25 to rate) only, if it is 3.500/4.250/5.000 the error is box 4 & 5 (Adding .125 in box 4 and .25 in box 5), if it is 3.625/4.375/5.125 boxes 1 & 2 (subtracting .25 for box 1 and subtracting .125 for Box 2) don't work and finally if i input 3.750/4.500/5.250 only box 1 (subtracting .25 from C11) does not work. other rates have all 5 working properly. so the problem is consistent but not sure why the same cells will work on some and not the other. the index match will find 2.875% on one and then not on the other.

Here is the "MarkToMarket" tab as well which would just be extended further down with each rate being formatted as a number and the rate conversion becing formatted as a rate to 3 decimal places.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rate Conversion[/TD]
[TD]Rate[/TD]
[TD]Program[/TD]
[TD]Coupon[/TD]
[TD]Month[/TD]
[TD]Morning Mark[/TD]
[/TR]
[TR]
[TD]=(Rate/100)[/TD]
[TD]2.750[/TD]
[TD]USFN3025[/TD]
[TD]2.5[/TD]
[TD]DEC[/TD]
[TD]96 9/32[/TD]
[/TR]
[TR]
[TD]=(Rate/100)[/TD]
[TD]2.875[/TD]
[TD]USFN3025[/TD]
[TD]2.5[/TD]
[TD]DEC[/TD]
[TD]96 9/32[/TD]
[/TR]
[TR]
[TD]=(Rate/100)[/TD]
[TD]3.000[/TD]
[TD]USFN3025[/TD]
[TD]2.5[/TD]
[TD]DEC[/TD]
[TD]96 9/32[/TD]
[/TR]
[TR]
[TD]=(Rate/100)[/TD]
[TD]3.125[/TD]
[TD]USFN3025[/TD]
[TD]2.5[/TD]
[TD]DEC[/TD]
[TD]96 9/32[/TD]
[/TR]
[TR]
[TD]=(Rate/100)[/TD]
[TD]3.250[/TD]
[TD]USFN3025[/TD]
[TD]3.0[/TD]
[TD]DEC[/TD]
[TD]99 29/32[/TD]
[/TR]
</tbody>[/TABLE]


I have tried "+ 0","*1", Trim, checked for blanks and checked formatting. Hopefully this is detailed enough as it has been driving me insane for a few days.

 

[Little_Clubber]

Are you able to send me the file to save me trying to re-type all your info to understand the problem better? littleClubber@gmail.com

 

[deverhart]

sent. Thanks Little_Clubber. I hope you can help. i feel like i must be missing something right in front of me.

 

[deverhart]

I was able to use a workaround to make everything flow. When looking up the rate for index match or vlookup i just put NUMBERVALUE() first and that solved the issue. shouldn't need to do it based on the formatting but it works.