how to make a list with products and prices

%3CLINGO-SUB%20id%3D%22lingo-sub-778629%22%20slang%3D%22en-US%22%3Ehow%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778629%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20do%20I%20make%20e%20productlist%20(dropdown)%20with%20the%20right%20price%3F%3C%2FP%3E%3CP%3ESo%20if%20I%20select%20for%20example%20banana%2C%20I%20want%20that%20the%20next%20cell%20automatically%20gives%20the%20right%20price%20for%20the%20banana.%20Is%20that%20possible%3F%3C%2FP%3E%3CP%3ETanx!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-778629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778846%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778846%22%20slang%3D%22en-US%22%3ERead%20this%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fvlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60150%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fvlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60150%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778925%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778925%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384155%22%20target%3D%22_blank%22%3E%40excelhelp%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20simple%2C%20click%20on%20Data%26nbsp%3B%20---%26gt%3B%20Data%20Validation%20---%26gt%3B%20List%20---%26gt%3B%20define%20the%20range%20of%20list.%3C%2FP%3E%3CP%3EPictorial%20illustration%20along%20with%20sample%20excel%20file%20is%20attached%20for%20more%20clarity%20and%20understanding%3A%20Thanks%2C%20Tauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20822px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124910i1349FF21980AE289%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Dropdown%20list.JPG%22%20title%3D%22Dropdown%20list.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781043%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781043%22%20slang%3D%22en-US%22%3E%3CP%3ETanx%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20make%20that%20list%2C%20but%20when%20I%20select%20a%20pear%2C%20the%20price%20doesn't%20change%20along.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781048%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781048%22%20slang%3D%22en-US%22%3E%3CP%3ETanx%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Bperhaps%20I%20am%20not%20following%20the%20instructions%20correctly%2C%20because%20it%20doesn't%20work.%20I%20get%20an%20error%20in%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781074%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781074%22%20slang%3D%22en-US%22%3EWhich%20error%20do%20you%20get%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781123%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384155%22%20target%3D%22_blank%22%3E%40excelhelp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20selected%20'%3CSTRONG%3EPear%3C%2FSTRONG%3E'%20and%20the%20price%20got%20changed.%3C%2FP%3E%3CP%3EAlternatively%2C%20you%20can%20share%20your%20file%20with%20list%20of%20values%20you%20need%20in%20a%20drop%20down%20list.%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20337px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125045iFDAEA1BCA1E20B25%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22DDL.JPG%22%20title%3D%22DDL.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781208%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20make%20a%20list%20with%20products%20and%20prices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384155%22%20target%3D%22_blank%22%3E%40excelhelp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20attached%20a%20sample%20file%3C%2FP%3E%3CP%3EThe%20drop%20list%20(data%20validation%20)%20in%20column%20A%20is%20changing%20Product%3C%2FP%3E%3CP%3EIn%20Column%20B%20I%20have%20a%20Vlookup%20%26amp%3B%20Match%20Function%20extracting%20the%20Price%20from%20a%20Master%20List%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(A3%2C%24D%243%3A%24F%2427%2CMATCH(%24B%242%2C%24D%242%3A%24F%242%2C0)%2CFALSE)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20947px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125052i49AEF5EE642A8E0C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PriceListTest.png%22%20title%3D%22PriceListTest.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20the%20Label%20in%20Cell%20B2%20is%20another%20drop%20list%20%26gt%3B.%20so%20if%20you%20switch%20that%20label%20from%20Price%20to%20color%20all%20the%20Vlookup%20functions%20return%20colors%3C%2FP%3E%3CP%3EConclusion%20the%20you%20should%20have%20a%20master%20list%20with%20all%20data%20and%20then%20using%20a%20drop%20list%20%26amp%3B%20a%20Vlookup%20function%20you%20pull%20out%20what%20you%20need.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

how do I make e productlist (dropdown) with the right price?

So if I select for example banana, I want that the next cell automatically gives the right price for the banana. Is that possible?

Tanx!

9 Replies

Hi @excelhelp  

Its simple, click on Data  ---> Data Validation ---> List ---> define the range of list.

Pictorial illustration along with sample excel file is attached for more clarity and understanding: Thanks, Tauqeer

 

Dropdown list.JPG

Tanx@tauqeeracma 

I can make that list, but when I select a pear, the price doesn't change along.

Tanx@Jan Karel Pieterse perhaps I am not following the instructions correctly, because it doesn't work. I get an error in the formula.

Which error do you get?

@excelhelp 

I selected 'Pear' and the price got changed.

Alternatively, you can share your file with list of values you need in a drop down list. Thanks

 

DDL.JPG

@excelhelp 

Hi

I attached a sample file

The drop list (data validation ) in column A is changing Product

In Column B I have a Vlookup & Match Function extracting the Price from a Master List

=VLOOKUP(A3,$D$3:$F$27,MATCH($B$2,$D$2:$F$2,0),FALSE)

PriceListTest.png

 

Also the Label in Cell B2 is another drop list >. so if you switch that label from Price to color all the Vlookup functions return colors

Conclusion the you should have a master list with all data and then using a drop list & a Vlookup function you pull out what you need.

Hope that helps

Nabil Mourad

Hi, I'm looking for a similar solution but can't figure it out.

I have a "Parts" column and a "Value" column. When I select a part, I would like the value cell to automatically update with the correct price.

I have another sheet with a table containing the parts and a value - this is where the data needs to come from.

Can anyone help?
Look in Help for the XLOOKUP function. If you do not see that function, try the VLOOKUP function instead.