Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    If dropdown is specific option, return associated value from another range.

    ✭
    edited 07/04/24 in Formulas and Functions

    Hi Community,

    I'm hoping that there is a simple way to do this (providing I explain it well enough).

    I've got an Order Summary with Product Type (dropdown) , Unit Quantity and Unit Rate columns, and a separate table (Product Summary) with the Product Type, Unit Rate, and Pricing Structure (pictured). I'm trying to achieve the following.

    1. A product type is selected in the dropdown column of the Order Summary.
    2. In the Unit Quantity column of the Order Summary, the Pricing Structure associated with that product type in the Product Summary is returned.

    I'm sure there's a relatively straightforward way to achieve this, so hoping I'm just missing the obvious here.

    Many thanks in advance!

    Hoarding_Example.jpg
    · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭

      Hi @david.mclean

      I set up your example where the far right of this is your product/rate/pricing table, and the far left is a dropdown with your various products preloaded. The column formula you would want in the 2nd column is:

      =INDEX([Unit Rate]:[Unit Rate], MATCH([Product Type]@row, [Product Name]:[Product Name], 0))

      Then whichever Product you choose in the first column, will pull in the matching Unit Rate automatically. I didn't put anything in here for the quantity but you could stick that qty column in, and easily do multiplication of unit rate x qty = extended price to round out your order details…I hope this helps!

      image.png
      · Share on FacebookShare on Twitter
    • ✭

      @Vince Darrigo thank you so much. I figured out a work around for the unit rate issue, but this really helped with assigning a pricing structure to each item for total cost calculation.

      Appreciate the help, continue being a wizard!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions