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

1. ## 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. ## 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

3. ## 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?
https://we.tl/YTZ2cnOR6B

4. ## 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.

5. ## 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.

6. ## 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. ## 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. ## Re: Excel giving out prices, taking in consideration of criteria.

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

9. ## 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. ## Re: Excel giving out prices, taking in consideration of criteria.

I think it's more than that: Special-K is having to do all the work for you ...

11. ## 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

12. ## 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. ## 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. ## 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. ## 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. ## Re: Excel giving out prices, taking in consideration of criteria.

Omg it worked, i found out how!!!

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

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

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

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

#### 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