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
mathetes's avatar
mathetes
Gold Contributor
Jul 09, 2020

Amanda535 

 

Without seeing your actual workbook, it's hard to be sure. I do notice that the first argument in your VLOOKUP formula is itself an array, and my own suspicion would be that that might be the problem. Usually one uses a single cell as the first argument.

 

If you were to post your actual sheet, we (I and others) could take a look at it. Just make sure it's not got any confidential info on it

    • 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