Forum Discussion

sarah_ode's avatar
sarah_ode
Copper Contributor
Mar 02, 2023

Formula Help - Like V Lookup but Multiple Criteria to Match

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" Imag...
excel
Formulas and Functions
FikturFox's avatar
FikturFox
Brass Contributor
Mar 02, 2023
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's avatar
    sarah_ode
    Copper Contributor
    Mar 07, 2023

    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's avatar
      FikturFox
      Brass Contributor
      Mar 11, 2023

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

Resources