Formula Help - Like V Lookup but Multiple Criteria to Match

Question

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

fikturfox · Answer

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.

sarah_ode · Answer

sarah_ode&nbsp;

sarah_ode · Answer

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

fikturfox · Answer

sarah_ode&nbsp;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)),"")

Forum Discussion

Formula Help - Like V Lookup but Multiple Criteria to Match

Share

Resources