+ Reply to Thread
Results 1 to 18 of 18

Excel giving out prices, taking in consideration of criteria.

  1. #1
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Post Excel giving out prices, taking in consideration of criteria.

    Good morning everyone, I'm having a little problem finding a solution for my cost simulation in Excel. Okay, imagine you have two products a T-Shirt and a pair of pants. Well that's the first criteria, then the two products you have them in two different sizes and colors which then is your second and third criteria. When all the criteria are clear I want Excel to spit out the correct price, does somebody know how to do that? I'm thinking of some sort of interactive drop down menu. Find here a scheme to understand my idea better : https://i.imgur.com/6r9GEBQ.jpg

    I hope somebody can help me and thank you for every tipp/idea!

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Excel giving out prices, taking in consideration of criteria.

    Here's one way, see attached file.
    This is an Array formula, use Ctrl-Shift-Enter
    Attached Files Attached Files
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Post Re: Excel giving out prices, taking in consideration of criteria.

    Thank You Special-K it works, unfortunate I'm having problems adapting it to my sheet... Could you help me out?
    Unfortunately the site does not let me upload the file, so here a link where you can download it.
    https://we.tl/YTZ2cnOR6B

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    Yes, the site DOES allow you to upload files, so please do so.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    https://www.excelforum.com/attachmen...1&d=1500370863

    Okay Thank you very much it worked there you go here is my file.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    Please reattach the sample file including your attempt at applying the formula given by Special-K and showing what your expected outcome is.

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Excel giving out prices, taking in consideration of criteria.

    @backdoor:

    OK I have your file. I see a list of data.
    So where are your selections and where is the output going to go?
    Supply some examples of input selections and expected output

  8. #8
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    Here it is, at the bottom. THANKS FOR YOUR HELP GUYS!!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Excel giving out prices, taking in consideration of criteria.

    Mm, I would have expected you have done something towards solving this like amending the cell references and ranges instead of just blatantly copying the formula direct and expecting it to work. The formula was ONLY going to work for your initial example data. Never mind, we'll skip that...

    Use this formula

    =VLOOKUP(F293,IF((A293=A$3:A$286)*(B293=B$3:B$286)*(C293=C$3:C$286)*(D293=D$3:D$286)*(E293=E$3:E$286),F$3:G$286),2,0)

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    THANKS FOR YOUR HELP GUYS!!
    I think it's more than that: Special-K is having to do all the work for you ...

  11. #11
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    It is true, he is doing my "job". The thing is I'm 15 years old and trying to learn Excel over the Internet and I pretty much fail haha. And I thank you guys so much! And I am so sorry to bother you again, but unfortunately I tried adopting the first Formula and the second too, I just doesn't work and I don't understand why, I think I am simply to stupid for this function...
    If you could have another look, I will send you a lot of cute cats/dogs pics
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    It's great that you are trying to learn! I spend my life teaching (yes, lots of 15-year-olds, too!) and I can tell you that one of the most important things for you as a learner is to get your hands dirty: try to apply what you have been shown, and then your teacher can help you tweak it. You wouldn't get an assignment from your teacher at school and then turn round and hand him or her your exercise book and pen and say, "Could you do it for me, please?", would you? That wouldn't be a worthwhile learning process, would it?

    No need for any apples for the teacher - just some effort from you to get you to grasp the concepts will be recompense enough.

  13. #13
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Excel giving out prices, taking in consideration of criteria.

    This is an what Excel calls an Array formula, you need to hold down Ctrl and Shift and then press Enter to add this formula.
    You cant just type the formula in and press Enter.

    If you use Ctrl-Shift Enter it returns the correct value of 288.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    Have a try with this:

    =INDEX($G$3:$G$290,MATCH(1,($A$3:$A$290=A293)*($B$3:$B$290=B293)*($C$3:$C$290=C293)*($D$3:$D$290=D293)*($E$3:$E$290=E293)*($F$3:$F$290=F293),0))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Of course, the easiest way to get the result you are looking for would be simply to filter the table ...

  15. #15
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    @AliGW you're right will try it again and again!
    @Special-K I tried to use the Ctrl-shift Enter way but it doesn't work, when I push the keys nothing happens...

    I tried both ways, unfortunately I fail at the ctrl-shift step...

  16. #16
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    Omg it worked, i found out how!!!

  17. #17
    Registered User
    Join Date
    07-14-2017
    Location
    Luxembourg
    MS-Off Ver
    2013
    Posts
    9

    Re: Excel giving out prices, taking in consideration of criteria.

    Omg it worked, i got it. I finally understood the iflookup formula!!!

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel giving out prices, taking in consideration of criteria.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel giving out prices, taking in consideration of criteria
    By backdoor in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-24-2017, 07:37 AM
  2. Replies: 2
    Last Post: 07-14-2015, 02:12 PM
  3. [SOLVED] Sum range without taking #N/A into consideration
    By Ramzes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2015, 08:31 AM
  4. pasting consideration between word & excel
    By vientito in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-01-2014, 10:58 AM
  5. Adding up Absent time taking lunch time into consideration
    By abrilabs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2013, 02:02 PM
  6. Replies: 3
    Last Post: 12-05-2012, 05:12 PM
  7. % to Volume Formula taking into consideration days worked
    By kwilliams5675 in forum Excel General
    Replies: 7
    Last Post: 04-20-2010, 03:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1