Formula Help - Like V Lookup but Multiple Criteria to Match

[sarah_ode]

I am trying to create a calculator that can link pricing from one sheet to another.

I am now stuck & have tried many different formulas but cannot get it to work.

• See "Shipping Calculator" Image
• Column "P"

Once you have added in a Postcode, Column L auto populates with the "Rate Zone"

When you select the Service (Which text links to "TNT Rates" Sheet - Row 9 or 15)

I would like Column P to add in the correct pricing per "Rate" from row 9 or 15 depending on the selection.

Can anyone provide any insight?

I don't know if it's the formulas I have tried, the way my data is set out of if it just isn't possible.

I have a spreadsheet I can send through!

"TNT Sheet"

"SHIPPING CALCULATOR" Sheet

[#2]

For column P, depending on the selection (service I guess), it will add the correct pricing per rate from row 9 or 15. So what would be the criteria if you choose row 9 or 15. What's with these rows (not shown in your image)? Could you attached the file please.

[#3]

@sarah_ode 

[#4]

@igorotXThank you so much for the response! I have attached the file.

I am unsure if this is possible to do or if my layout needs to be modified slightly to achieve this?

- Row 9 is for if Overnight Service is selected

It then needs to refer to column B-K to see which "Rate" to drop in for pricing

- Row 15 is for if Road Service is selected

It then needs to refer to column B-K to see which "Rate" to drop in for pricing

[#5]

@sarah_ode 

Try this in P7...

=IFERROR(INDEX('TNT Rates'!$A$5:$K$16,MATCH(D7,'TNT Rates'!$A$5:$A$16,0),MATCH(L7,'TNT Rates'!$A$5:$K$5,0)),"")