Dropdown to determine value on another column

A little help...

I want to use a dropdwon column value to populate another cell with a predetermined value from a lookup column.

The attached screenshot - Unit Type is the drop down and I want it to correlate to a value from pricing columns and populate the unit rate column.

Find more posts tagged with

Project Management

Comments

Paul Newcome

Are you able to provide more details? What are the dropdown options? How are you determining which column to pull from?

Shawn O'Banion

Paul,

Column 5 in the screenshot are the dropdown values. As far as determining what column to pull from that will eventually come from another dropdown determining size of locale - Major Metro, Mid-Size Metro, or Rural.

Thanks for the assistance

Paul Newcome

Ah. Ok. So for this example I am going to shorten your column names a little bit.

=INDEX([Major Metro]:[Major Metro], MATCH([Unit Type]@row, [Column5]:[Column5], 0))

This will pull the price from the Major Metro column for whatever row the Unit type matches in Column 5.

To include the second dropdown, you would make some basic adjustments where we specify the column number portion of the INDEX function. The easiest way (but not the most flexible) would be to use a nested IF statement.

So....

IF([Size of Locale]@row = "Major Metro", 1, IF([Size of Locale]@row = "Mid-Size Metro", 2, 3))

What this does is populates a 1 if it is Major, 2 if it is Mid-Size, and 3 for Rural. Once we drop this in to the column indicator portion of the INDEX function and expand your initial range to include the additional columns, you should be set. Obviously you will need to change column names and verbiage of criteria to match your exact needs.

=INDEX([Major Metro]:Rural, MATCH([Unit Type]@row, [Column5]:[Column5], 0), IF([Size of Locale]@row = "Major Metro", 1, IF([Size of Locale]@row = "Mid-Size Metro", 2, 3)))

Shawn O'Banion

Paul,

This is great. I will give it a try.

Thanks for the help, much appreciated.

Shawn

Paul Newcome

Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…