Table parametrization with index and match command

[aggelos]

hello everybody, I have a problem with excel if command and I need your help I have let's say the below columns

distance calendar price

200km 12 months 10€

400km 24 months 17€

600km 36 months 29€

700 km 48months 33€
the distance might be a random number between 200 and 700

I want to create a larger table with distance as rows for every 100km until 700km (or every 50km until 700 or every 75km it doesn't matter the step) and months as columns for every six months until 48 and for every element of this table to fill it with the appropriate price from the first table. for example

months

6months 12months 18months 24months 30months 36months 42 months 48months

100km 10€ 10€ 10€ 17€ 17€ 29€ 29€ 33€

200km 10€ 10€ 10€ 17€ 17€ 29€ 29€ 33€

300km 10€ 10€ 10€ 17€ 17€ 29€ 29€ 33€

400km 17€ 17€ 17€ 17€ 17€ 29€ 29€ 33€

500km 17€ 17€ 17€ 17€ 17€ 29€ 29€ 33€

600km 29 29€ 29€ 29€ 29€ 29€ 29€ 33€

700km 33€ 33€ 33€ 33€ 33€ 33€ 33€ 33€



How is it possible to do that automatically with commands it if anyone could create one command for this issue? I upload an image for a better example

 

[aggelos]

hello again I send you two images the first one (mine table) I have filled the table manually and the second one is the table according to your equation, you can see the differences. ps I think own you a beer

 

[fjns]

Hi, I created the newest table based on your table.

The new formula used in the table:
=IF(OR($G3="",H$2=""),"",INDEX($C:$C,MAX(IFERROR(MATCH($G3,$A:$A,0),IFERROR(MATCH($G3,$A:$A,1)+1,2)),IFERROR(MATCH(H$2,$B:$B,0),IFERROR(MATCH(H$2,$B:$B,1)+1,2)))))


Parametrization4.xlsx