Create a two column pull down list

[SJGWeb]

I have something that seems simple, but I can't seem to figure it out.

I have a list on Sheet 1. In column A it contains model numbers and descriptions. In column B it contains prices for the adjacent A column cell. I want to create a pull down list on a new sheet, or even on a nearby location on the same sheet so that I can select a model#, have it appear in the pull down column, and have the product's price appear in the adjacent column.

So, for example, in a pull down menu in cell D1, I select Sprocket 22 from A1. I want the pull down cell to show Sprocket 22, and cell D2 to show the price in cell B1. Once selected I can have formulas referencing the price in the D2 cell filled in by the pull down list. Can't find an easy way to do it, and ActiveX controls are a bit beyond my expertise.

I use Excel from Office Pro 2016. Thanks for your help!

[Beamernsw]

A sample sheet, as per the bright yellow banner at the top, would be very helpful.

I think index/match would be what you want.
Your thread title is a little confusing, unless I'm misinterpreting what you want. I'm thinking it's more like "price lookup from drop-down selection" maybe..
If my description is what you want then index/match should work.

For your needs: INDEX(array, row_num, column_num)
This lets you select a lookup area (array). Then you can pull info from a cell by targeting the row no. and column no.

If you used =INDEX(A1:B100, 15, 2) it would return the value from cell B15. That is the 15th row in the table and the 2nd column.
What you would now need is MATCH to find the item from the first column.
For your needs: MATCH (lookup_value, lookup_array, 0). The 0 at the end tells it to look for an exact match.
=MATCH("Sprocket 22", A1:A100, 0) This will look from A1 to A100 for "Sprocket 22" and return the row that its found in.
So, to combine them:
=INDEX(A1:B100, MATCH("Sprocket 22", A1:A100, 0), 2)
This will search for "Sprocket 22" in cells A1:A100, use that as the row number for index, and we know that column 2 holds the prices, so this will return the price of a "Sprocket 22".
Now all you need to do is swap "Sprocket 22" in the formula for the cell reference of your drop down (D1)

Final formula for cell D2: =INDEX(A1:B100, MATCH(D1, A1:A100, 0), 2)

Edit:-
Apologies if I interpreted your request incorrectly and this was no help to you. If that is the case, then please supply a small sample sheet showing what you want.

Thankyou, Beamer.

[SJGWeb]

Thank you. I don't think this will work because you need to know the cell in advance. I've uploaded a spreadsheet but since I can't achieve what I want, I'm not sure how helpful it will be but here goes.

In column A we have 5 different Widgets. In column B we have the costs of those Widgets. In column D we have a pull down list using the data validation tool. What I want to have happen is to pull down the list, select a Widget, and have it fill in the cost in the adjacent column E.

[Imbizile]

I think what you are looking for, can be solved with a very simple vlookup. See attached file

[SJGWeb]

Yep, VLOOKUP was the right Function. Thanks!

[Beamernsw]

I just entered the formula in that I suggested and it worked perfectly without any change. I don't know why it didn't work for you.
INDEX/MATCH is much more resilient then VLOOKUP. VLOOKUP can fail at times and I think all your data in that column needs to be alphabetical, but not too sure. I used to use vlookup a lot until the good guru's here showed me how easily it can fail compared to index/match.

Either way, I'm glad you got it sorted.

[SJGWeb]

Thanks. For this little spreadsheet VLOOKUP is enough. I really appreciate your feedback!