Auto fill a second column based on a drop down selection using a form

Dajah

I understand I can likely do this with a very, very long VLOOKUP  or IF/THEN formula in every cell, but I'm dealing with over a hundred items and that will be unwieldy. 

Situation:

I would like to have a form where someone selects one option from a dropdown of ~100 items. That value then populates into a sheet. (This seems to be the easy part.)

Once the dropdown value is on the sheet, I would like a linked value to populate in the column next to it. 

Example: Someone selects "Space Heater" from the dropdown menu on the form, that item populates into Column 1 of the sheet, and then Column 2 automatically fills in the amount "$75" 

I have a separate sheet for source data which lists all dropdown options in one column and all prices associated with it in another column, so this sheet could be referenced. 

I do not want this linked value (in this case the price) populating visibly on the form, as the people filling out the form would not be privy to sales prices. 

The practical application here is a person documenting our items they've brought to clients. We want to track what items are being dropped off, and what their sales price is. 

Any help would be greatly appreciated! Thank you.

Mike Wilday

Your vlookup should actually be fairly short! I'll break it down for you... 

 

=Vlookup([Dropdown Column Title]@row, 

• this is looking at your dropdown column. Replace my verbiage with your actual title. 
• After you type in the comma, look in the dropdown from the formula box, and choose reference data from another sheet. 
• https://help.smartsheet.com/articles/2476606
• Select your data source, and both columns for referencing. 
• You should see something similar to the following. 

=Vlookup([Dropdown Column Title]@row, {Your Reference Title}

=Vlookup([Dropdown Column Title]@row, {Your Reference Title}, 2, false)

• Now add a comma after the reference title and the number 2 since you want the 2nd column of your data to be retrieved. 
• Add another comma and the word false which indicates the need for an exact match. Then close the formula. 

https://help.smartsheet.com/function/vlookup

Let me know if you have trouble getting it to work. You'll then want to grab the lower-right hand corner of the cell and drag it into the rest of your form entries. New entries should acquire the formula from the rows before it (or after if new entries appear at the top of the list). 

cslatten

You could use this formula: =INDEX({Number List Range 3}, MATCH([Part Number]6, {Number List Range 2}, 0))

{Number List Range 3} = Column with Prices

[Part Number]6 = Cell with your dropdown list

{Number List Range 2} = Column with Your parts list

use 0 with the MATCH function to get an exact match(case sensitive) 1 and -1 can be used for an approximate match(results may vary)

INDEX will return a value within a given range if you specify what row and what column it is in within that range. The column number is optional if you only have one column in your range.

INDEX(range,row,column)

if you substitute Match for the row portion. Match will return a value of the row it finds the match.

MATCH(find, in this range of cells, Exact(0) or Approximate(1,-1))

You can reference another sheet by clicking the link. and selecting the range of cells you want.

 

Hope this helps.

Sorry if it is confusing. I am not good at explaining things

 

Dajah

I think I'm missing something at the beginning- I'm getting a circular reference error. Maybe I copied something verbatim that should have been filled with my own info?

My source data sheet is called "Price Lookup PRODUCT." Column 1 is Item and column 2 is Price. I am selecting both of these columns for the lookup_table part of the formula.

The destination data sheet is where the form would be sending information. Column 1 is Item, which is chosen from a dropdown menu in the form. This dropdown menu's options are verbatim what is in the Item column of the source sheet. 

=VLOOKUP(Item@row, {Price Lookup PRODUCT Range 2}, 2, false)

 

Can you tell what I'm doing wrong? I appreciate the help!

Dajah

THIS WORKED! Thank you so much.

Mike Wilday

Hmmm. In theory, I don't see anything wrong. Does your Range 2 in your formula include both columns from the lookup page? 

Dajah

I figured it out! And it works great. I used the INDEX function recommended below but apparently I can't use the same data set twice on the same sheet? When I update one, it updates the other. VLOOKUP it is. Thank you!

Mike Wilday

Awesome. Glad I was able to help! 

Kelly M

Seems so simple...but.  I must be missing something.  Any guidance would be much appreciated.

I'm trying to get a VLOOKUP to work to do just what this post discusses.  I'm similarly having trouble.

My primary sheet has a column: "State" that is a dropdown list.  When State is populated from the dropdown, I want a column "Color" to auto-populate from another sheet called "Color Lookup".  Here's the formula I'm using:

=VLOOKUP([State]@row, {Color Lookup}, 2, false)

where "State" references the dropdown list in my primary sheet and "Color Lookup" is a simple list in the secondary sheet:

State    Color

WA       Blue

OR       Red

ID         Green

Can anyone see what I'm missing please?  I'm getting a #INVALID REF error. I've confirmed the column names match (i.e. State and Color), the name of the lookup sheet is correct and "Color" is column 2 in the lookup table.

Thank you!

Kelly

Mike Wilday

Hi Kelly, 

The error you are getting is because its saying the reference doesn't exist. I would delete the reference to the other sheet and try recreating it. Make sure you select both the state and color column when you recreate the reference. 

Kelly M

Hi Mike ~ Awesome, that did it!  Thank you very much!

Kelly

 

Mike Wilday

Great! Glad I could be of service. 

Bradley Casado

I am also having an issue getting this to work.

I am trying to get the value of a dropdown list to automatically display a text on another column.

For some reason the first two rows work but then the rest just duplicate the value from the first row:

This is the second sheet that i'm referencing:

I am using the following formula:

=VLOOKUP([Column2]@row, {New Sheet 2 Range 1}, 1)

Can you please let me know where I am going wrong? any help will be appreciated.

Andrée Starå

Hi @Bradley Casado,

Try changing the 1 in the end to 0.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Bradley Casado

Changing the 1 to 0 gives me (#Incorrect Argument Set)

Andrée Starå

Sorry about that!

I misread the post.

Try this.

=VLOOKUP([Column2]@row, {New Sheet 2 Range 1}, 1,false)