Need a forumla to take 3 factors to find a solution from a table

[warren_richards04]

Hi everyone

After the very helpful thread that I recently wrote, I need help with the last stage of my Excel file - thanks to anyone who tries and/or can help!

I have attached the file; now that my file tells me, from the initial three columns, which band the price falls into, I just need the cost box to:

- take note of the gpm (above) - telling it which of the two tables to go to in the 'tariff' sheet
- take note of the volume - telling it which row to go to in the 'tariff' sheet
- take note of the band - telling it which column to go to in the 'tariff' sheet

All of which should then bring it to a price!

I've tried by am really struggling - any help would be greatly appreciated.

Thanks, Warren

[DonkeyOte]

Based purely on your sample file:

F7: =INDEX(tariff!$B$5:$G$28,MATCH($B$4,tariff!$A$5:$A$14,0)+14*($B$3="20-40"),MATCH("* "&TRIM($E7),tariff!$4:$4,0))
copied down

[warren_richards04]

Thanks a lot.

I will try this, but how easy do you think it might be to explain this, as I need to extend it to a wider sample and wonder whether I can learn the logic behind it?

[DonkeyOte]

The example is quite specific I grant you and actually contains an error...

please replace tariff!$4:$4 with tariff!$B$4:$G$4

In reality it assumes the tariff tables are identical and that from each "Duration" cell there are 14 cells before the next "Duration" cell.

With the above assumptions accounted for it matches the Volume against the values in Col A on the first tariff table to determine row_index position of result - to which it adds 14 if looking at the 2nd table.
It subsequently matches the Band against the headers of the first tariff table to determine column_index.
The intersect of row_index & column_index within specified range is the value of interest.

If the tariff tables are not all identical then the above would need to be revised. Others may suggest naming your tables and using INDIRECT etc...

[warren_richards04]

Thanks - this is very helpful.

I have tried doing this in my master file, but it doesn't seem to work... the formula stays but it says N/A as the result.

Here is the formula:

=INDEX(Tariff!B5:Tariff!G70,MATCH(B4,Tariff!B5:B14,0)+14*(B3="20-35")+28*(B3="35-60")+42*(B3="60-80")+56*(B3="80-100"),MATCH("* "&TRIM($E7),Tariff!B4:F4,0))

Any idea what I have done wrong? I've added in 28, 42 etc. for the new tables and options.

Thanks in advance

[warren_richards04]

Panic over - I have found it! I have the wrong parameters to match the index running down the side.

You are a genius, thank you so much - this is amazing.

[DonkeyOte]

If you have lots of tariffs I confess I'd dispense with the IF - I used that based on 2 table example


MATCH($B$4,Tariff!$B$5:$B$14,0)+14*($B$3="20-35")+28*($B$3="35-60")+42*($B$3="60-80")+56*($B$3="80-100")

becomes

MATCH($B$4,Tariff!$B$5:$B$14,0)+14*(MATCH($B$3,{"0-20","20-35","35-60","60-80","80-100"})-1)

or

MATCH($B$4,Tariff!$B$5:$B$14,0)+LOOKUP($B$3,{"0-20","20-35","35-60","60-80","80-100"},{0,14,28,42,56})