Help with looking up value in table

[gjennings4]

Hi all

I'm hoping someone can help me with a formula or some advice.

I'm putting together a spreadsheet (example spreadsheet attached) which calculates customer prices for them. We sell corrugated board and the customer's price is calculated by the overall size of the sheet multiplied by their price per 1000 square metres.

What needs doing to this spreadsheet is filling cell C9 in by cross referencing the material chosen in the drop down (C5) and the total sqm (G9) in the table at the bottom to find the price per 1000 sqm. The trouble we’re having is using the G9 figure to look up a range (eg 200-499).

So for example if I wanted 500 sheets of 125K/125T/B sized 1200 x 1500 (900sqm in total- as per G9) I would want the table to find the value in cell D16 (between 500-999 sqm and material = 125K/125T/B) and put the value in C9.

Is there a formula which can sort this out? If someone can help me I will be forever in your debt!

*please note prices supplied are just dummy figures, not real prices- I don't fancy getting fired!*

[JohnTopley]

Try ....

=INDEX($C$16:$H$19,MATCH($C$5,$A$16:$A$19,0),MATCH($G$9,$C$15:$H$15,1))

See attached

[oeldere]

with index/match

I changed the blue cells in row 15.

The result is in the green cell C9.

See the attached file.

[gjennings4]

you absolute beauties, thanks very much!

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.