Auto fill a second column based on a drop down selection using a form
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.
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.