Forum Discussion

Amanda535's avatar
Amanda535
Copper Contributor
Jul 09, 2020

Need to calculate based on values from two separate drop down Lists

list of 16 cars, 16 brands and 16 different prices. I have a main sheet with my data, a second sheet with the table containing the drop down lists. My formula spills. I cannot for the life of me figu...
Formulas and Functions
  • Amanda535's avatar
    Amanda535
    Copper Contributor
    Jul 20, 2020
    You guys are the absolute best! I didnt even try the match thanks so much for you help. Now I'll never forget it
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 20, 2020

      Amanda535 

      You are welcome.

      If continue mathetes idea, you may name range with Mileage Matrix let say as MileageMatrix, when formula could be

       

      =IFNA(INDEX( MileageMatrix,
       MATCH([@[From ]],INDEX(MileageMatrix,0,1),0),
       MATCH([@To],INDEX(MileageMatrix,1,0),0)
),0)
  • mathetes's avatar
    mathetes
    Gold Contributor
    Jul 20, 2020

    @Amanda535 

     

    SergeiBaklanhas given you the formula that handles your initial request. I've tweaked the spreadsheet just a bit more and suggest you not "hard-code" the mileage rate into the formula, as you had it. Instead, create a named range where that rate is called "MileageRate" and then your formulas will just refer to that term. That way, next year, when the rate gets changed (or after you've exceeded the 5,000 Km limit, the rate can be changed at the level of that little table rather than editing each formula.

Resources