Need help creating a list

[ergibbs]

I have forgotten so much of what I used to know and this should be easy. I have a range of item names with their associated prices. If I select an item (from a drop down list), I would like to populate the adjacent cell with the associated price. Help?

[Sam Capricci]

Use VLOOKUP (and please change your post title per forum rule 1)

[ergibbs]

See? I'm getting so old I forgot the forum rules.

If I use VLOOKUP, do I have to enter each item manually, or can I select a range? Can you give me an example? Thanks

[ergibbs]

I don't see how to change the subject line

[Sam Capricci]

usually a dropdown places a value from the dropdown list in a specific cell, for example A2. A vlookup will look at one column and over a certain number of columns and return a corresponding value of that cell. So for example say your value is in A2 and in another area of that sheet are the values that correspond to the dropdown and across from them the price. The vlookup looks like =vlookup(A2,D:E,2,false) where the related value to A2 is somewhere in column D and the price is across from it in column E and so you are saying, look for A2 somewhere in col D and when you find it go over to the second column (the 2 in the formula) and return what you find there and the "false" means that the match for this needs to be exact.
Hope that helps.

[Sam Capricci]

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.
from the forum rules page.

[Sam Capricci]

thanks for changing the thread post title.
attached are two examples of how to use the vlookup, one in the same sheet, the other in sheet2.
hopefully that will help (because I'm using excel for mac I can't add a dropdown in the example but you can see how it would work).

[ergibbs]

Thanks for the info. If I understand correctly, the formula would have to contain the value that needs to be looked up. That would be difficult with 30 plus entries. Is there a way to select an item from a drop down list, and then have the price entered into the adjacent cell automatically?

For example, here's a short list

Item A 3.99
Item B 4.99
Item C 5.99


So if I select from a drop down list Item A for a cell, the associated price of 3.99 would be placed in the adjacent cell. It appears as though the VLOOKUP would require me to create an entry for each value. Am I missing something?

[Sam Capricci]

unfortunately I can't create a dropdown in excel for mac, but if you change the product in sheet1 from cars to balls or shirts the prices in B and C will both change to correspond to the new item.
This sounds like what you want. A lookup table and formula will have to have a reference table to use against. So somewhere else Item A at 3.99 and Item B at 4.99 and Item C at 5.99 will have to be entered so the formula can reference it.

[ergibbs]

OK, I'm making progress - thanks. However, something seems to not be working properly. Here is the formula you provided - =VLOOKUP(A2;F:G;2;FALSE)

Using your sample, if I change "cars" to "pearls," the value in column B does not change to reflect the price of the pearls.

I think, once I figure out the formula, I can create the drop down list without a problem.

[ChemistB]

If you change "cars" to "Pearls" in A2? If changing A2 is not changing your result, try hitting F9 (which means your spreadsheet is set to manual calculation).

[ergibbs]

F9 worked. Is there a way to set it to automatic calculation?

[ChemistB]

File tab>Options>Formulas
Change from manual to automatic
Find it?

[ergibbs]

Sweet, thanks