Dropdown to determine value on another column

Shawn O'Banion

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.

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.