Lookup rates in Excelsheet based on 'binning/buckets '/ instead of full data modification

[marccator]

Hello,

I have a file, which shows an interest rate % based on LTV and fixed year term (10yr). The interest rate table is based on columns of LTV's <60%-60,01-70%-70,01%-90% and 90,01+>

Currently, I have the table modified that each LTV % is showed separately. This is time consuming and is niet helpfull when LTV's are 2 figures after the comma.
However, Which formula do I need to use, in order to generate the same results, but with easier input.
I have attached the file, and I think the formula should be placed in the yellow cell.

[JohnTopley]

Change data in Row 18 as per attached

=INDEX($B$19:$E$19,MATCH($C$23,$B$18:$E$18,1))

Change commas to semi-colons (local settings)

[marccator]

Hi John,

Thank you for your yquick reply, but the rates don't reflect the good rates.
E.g. a bucket shows a rate =<60%..
When having an input of an LTV of 60,09%, it should show the rate corresponding to the <80% bucket.
I have attached a new file.

Could you please help me how to fix this?

[JohnTopley]

Change table as per attached.

[marccator]

Hi,

great John, thank you very much. It helped me through me with picking the rates.
However, I stumbled into another problem, which it when I use index match with 2 different values. For some reason, Excel picks the column left to the one which should be picked.
I copied the right formulas from your sheet, but I can't see what's going wrong. The setup is similar, but it doesn't work.
This is the final issue before I can complete my sheet, so I hope you can have another look to help targetting my problem.

[JohnTopley]

Try

=INDEX(B5:M96,MATCH(P3,A5:A96,1),MATCH(P4,B4:M4,1))

[marccator]

Hi John, thank you very much! It works !