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

 

[fjns]

Hi, see the linked file for a possible solution...

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

Parametrization.xlsx

 

[aggelos]

Hi, see the linked file for a possible solution...

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

Parametrization.xlsx

View attachment 88152

Hi, see the linked file for a possible solution...

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

Parametrization.xlsx

View attachment 88152

thanks a lot but when I type it I receive an error from excel that says
There's a problemwith this formula
Not trying to type a formula?
When the first character is equal ("=") or minus ("-") sign , Excel tniks it's a formula
etc

 

[fjns]

Sorry, I mistakenly copied the formula from the table.

The correct formula is:
=IF(OR($G3="",H$2=""),"",INDEX($C:$C,IF(IFERROR(MATCH($G3,$A:$A,1),2)>IFERROR(MATCH(H$2,$B:$B,1),2),IFERROR(MATCH($G3,$A:$A,1),2),IFERROR(MATCH(H$2,$B:$B,1),2))))

 

[aggelos]

Sorry, I mistakenly copied the formula from the table.

The correct formula is:
=IF(OR($G3="",H$2=""),"",INDEX($C:$C,IF(IFERROR(MATCH($G3,$A:$A,1),2)>IFERROR(MATCH(H$2,$B:$B,1),2),IFERROR(MATCH($G3,$A:$A,1),2),IFERROR(MATCH(H$2,$B:$B,1),2))))

thanks a lot that works

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[aggelos]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

hello again may I ask you something else? how is it possible from the large table that I have to create a smaller one for example a table that have data between rows G7 up to G12 and columns from K up to Q

 

[fjns]

Hi, I created a new table

Parametrization2.xlsx

 

[aggelos]

Hi, I created a new table

Parametrization2.xlsx

Hello again, sorry to bother you but I just noticed that concerning distance the price is 10€ until 300km but according to the table A2:C7 the price should have been 10€ until 200km and from 250km up to 350km 15€ ,the same regarding date up to 24 the price should have been 10€ and for 30 and 36 price =15€ etc. For example in distance 700km and date 54 the price should have been 21 € and not 18€ or for distance 800km and date 60 the price should have been 37€ and not 21€

 

[fjns]

Hi, I created another table.
I hope I understand what you wrote correctly.

Parametrization3.xlsx