SOLVED

Need help with formulas Please

%3CLINGO-SUB%20id%3D%22lingo-sub-3096158%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formulas%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3096158%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20racking%20my%20brain%20for%20days%20trying%20to%20figure%20out%20how%20to%20do%20this%20-%20I%20need%20to%20pull%20the%20pricing%20data%20from%20another%20sheet%20based%20on%20same%20criteria%20in%202%20columns%20that%20have%20drop%20downs%20on%20the%20main%20sheet.%20Sorry%2C%20not%20sure%20if%20I'm%20asking%20the%20right%20questions.%26nbsp%3B%20I'm%20needing%20this%20to%20be%20able%20to%20provide%20estimates.%26nbsp%3B%20The%20%22unit%20cost%22%20data%20is%20on%20Sheet%202.%20The%20criteria%20must%20be%20met%20in%20column%20A%26nbsp%3B%20(2x4x8)%20and%20column%20B%20(pressure%20treated)-%20Both%20of%20these%20columns%20are%20drop%20down%20menus%20-%20(%20pressure%20treated%2C%20cedar%2C%20composite).%26nbsp%3B%20Sheet%202%20has%20the%20pricing%20data.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20need%20the%20price%20to%20change%20depending%20on%20what%20is%20selected%20from%20the%20drop%20down%20menus.%26nbsp%3B%20Is%20this%20even%20possible%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20to%20be%20pretty%20good%20at%20excel....%2015%20years%20ago!%20Had%20a%20career%20change%20but%20now%20trying%20to%20help%20my%20son%20out%20with%20his%20new%20business%20building%20decks.%26nbsp%3B%20%26nbsp%3BThanks%20for%20your%20help!%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%20image-alt%3D%22julieshea_2-1643673730024.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344054i3200776D6CF4BD7D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22julieshea_2-1643673730024.png%22%20alt%3D%22julieshea_2-1643673730024.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESHEET%202%20%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22julieshea_1-1643673486511.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344053iFB7101353305B20D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22julieshea_1-1643673486511.png%22%20alt%3D%22julieshea_1-1643673486511.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3096158%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3096931%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formulas%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3096931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1291636%22%20target%3D%22_blank%22%3E%40julieshea%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20file%20will%20help%20you%20get%20back%20into%20Excel.%20I've%20demonstrated%20INDEX%20combined%20with%20MATCH%2C%20using%20named%20ranges%20in%20Sheet2%20to%20make%20the%20formula%20easier%20to%20write%2C%20read%20and%20maintain.%3C%2FP%3E%3CP%3ESheet1%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-02-01%20at%2006.24.25.png%22%20style%3D%22width%3A%20547px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344103i79391D9BE0E9202E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-02-01%20at%2006.24.25.png%22%20alt%3D%22Screenshot%202022-02-01%20at%2006.24.25.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet2%3A%20Each%20of%20the%20coloured%20areas%20is%20a%20Named%20Range%20(prices%2C%20sizes%20and%20materials).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-02-01%20at%2006.24.35.png%22%20style%3D%22width%3A%20446px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344104iF470DF166AE64F41%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-02-01%20at%2006.24.35.png%22%20alt%3D%22Screenshot%202022-02-01%20at%2006.24.35.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3114698%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formulas%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3114698%22%20slang%3D%22en-US%22%3ETHANK%20YOU!%20It's%20slowly%20coming%20back%20to%20me%20but%20would%20have%20taken%20me%20forever%20without%20your%20help!%20Thanks%20so%20much!%3C%2FLINGO-BODY%3E
New Contributor

I have been racking my brain for days trying to figure out how to do this - I need to pull the pricing data from another sheet based on same criteria in 2 columns that have drop downs on the main sheet. Sorry, not sure if I'm asking the right questions.  I'm needing this to be able to provide estimates.  The "unit cost" data is on Sheet 2. The criteria must be met in column A  (2x4x8) and column B (pressure treated)- Both of these columns are drop down menus - ( pressure treated, cedar, composite).  Sheet 2 has the pricing data. 

So I need the price to change depending on what is selected from the drop down menus.  Is this even possible?   

I used to be pretty good at excel.... 15 years ago! Had a career change but now trying to help my son out with his new business building decks.   Thanks for your help!

 

julieshea_2-1643673730024.png

 

 

SHEET 2 >

julieshea_1-1643673486511.png

 

 

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@julieshea Perhaps the attached file will help you get back into Excel. I've demonstrated INDEX combined with MATCH, using named ranges in Sheet2 to make the formula easier to write, read and maintain.

Sheet1:

Screenshot 2022-02-01 at 06.24.25.png

 

Sheet2: Each of the coloured areas is a Named Range (prices, sizes and materials).

Screenshot 2022-02-01 at 06.24.35.png

THANK YOU! It's slowly coming back to me but would have taken me forever without your help! Thanks so much!