Auto populate a cell with a price

[oscarlimerick]

Hi folks I’m guessing I use the lookup function for this but I want to do a drop down arrow for items and have it auto populate a cell with the price for that item. So for example hoodies cost $5.99 and hats cost $3.99. I want to have column a show that item based on a drop down menu that would display the item hoodie or hat, and I want the column b to display the price of the item, $5.99 or 3.99 in this example. Any suggestions please and thanks

[Marcia]

How about doing a validation list taken from a list of items sold? I have attached a sample. In sheet 1, you add your product/item and their corresponding prices.

[oscarlimerick]

Hi Marcia. looking at your example spreadsheet, although it does pull in product hoodie from the first tab 1 as a product in cell B4,, it does not bring in the price value for hoodie beside that in cell C4. Am I doing something wrong? If I try and edit cell C4, I get an error saying "You can't change part of an array". Am I doing something wrong here? Many thanks

[Marcia]

I’m using Office 365. Try re entering the INDEX/MATCH formula. It should return the price of the item that you selected.

[Marcia]

Or, recreate a new table. Type the headings. Click mouse in the celle below product>>Open the Data tab>>Open Data Validation in the Data Tools group>>Open the dropdown of the Allow: box, select List>>in the Source: box type =Product>>OK
Under price, enter the code
=IFERROR(Index(Price,Match(B3,Product,0)),””)

[oscarlimerick]

I am having no luck with this method is there a way to do this using vlookup or something?

[Marcia]

=VLOOKUP(C3,Table1,2,FALSE)

[Pecoflyer]

Be careful with IFERROR, it hides ALL errors which might not be what you want

[ArviLaanemets]

Slightly edited version of workbook from Marcia.

Both tables are defined as Tables (this ensures, you don't have to edit formulas whenever you add new data to either of Tables);
The products list referred in Data validation is read as column of product list Table and is now dynamic;
To get price for data entry table, VLOOKUP is used instead of INDEX;
All formulas are using Table syntax.

[oscarlimerick]

This all works but I don't understand why it's necessary to have the iferrror in there, what does this do? Can I get the reuslts i want just using vlookup? How would I do this? Thanks

[ArviLaanemets]

Originally Posted by oscarlimerick

This all works but I don't understand why it's necessary to have the iferrror in there, what does this do? Can I get the reuslts i want just using vlookup? How would I do this? Thanks

In case VLOOKUP for some reason don't find the searched record, it returns an error, and IFERROR handles such cases. It will be especially useful when you have calculated controls on your form - in case a source for such calculations returns an error, the calculation itself returns an error too.

You can of-course simply ignore this, but taking care of such cases, however small the probability of them will be, is simply a good programming style.

[oscarlimerick]

Ok, good to know, I have found all the answers to my questions so I will mark this as resolved. Thanks to everyone who helped me through this