Formula Help - Like V Lookup but Multiple Criteria to Match

Copper Contributor

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"

TNT Sheet.png

"SHIPPING CALCULATOR" Sheet

 

Shipping Calculator Sheet.png

 

 

4 Replies
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.

@FikturFoxThank 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

@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)),"")