Home

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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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
excelhelp
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!

7 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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies