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

david.mclean

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!

Vince Darrigo

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!

david.mclean

@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!