Forum Discussion
Formula - Auto Fill cell dependent on variables input in multiple cells
Hello all,
I am newer to using Excel formulas and am stuck on how to create a formula in which the value of cell 'D'(discount percent) will be dependent on the values input in cell 'C'(quantity), and cell 'A'(animal). The value of cell 'C' is referenced against a range of integers.
For example, say I want to purchase cats. I want to purchase 1 cat:
A = Cat
C = 1
D = How would a write a formula to lookup a series of tables to identify that if cat and equal a 'quantity range' (0 - 1 in this example), then Discount % = x (0 in this example)
Or
A = Dog
C = 15
D = How could a formula be created to identify that if Dog and quantity of 17, then discount % value of 18 would be auto-fill in cell D?
I have an Excel sheet started where my 'products' (animals in this example) are being selected from a drop-down menu, as well as a VLOOKUP which links my 'list price' to the value selected from my drop-down menu. But, I am struggling to find a way to have a Discount % value auto-fill in another cell, based on the product type and quantity input in 2 separate cells.
Any hints or ideas on what I should be looking to use here?
Thanks!
Mike
9 Replies
Herewith a slightly revised version. This time including CatDiscount. And a minor correction to the DogDiscount table itself: I realized I hadn't handled the very top end correctly.
You're very welcome. Feel free to come back and ask more questions if you need to.
I learned something myself in the course of responding, using a combination of functions for the first time (INDIRECT and TEXTJOIN). The latter is relatively new, I believe.....and I liked how well they could work together to enable VLOOKUP to refer to different tables just by creating the table name on the fly in the VLOOKUP formula itself.
This was fun!
Here is a glimpse of what's in the attached spreadsheet.
You obviously know how to do most of this, so I won't go into all the details.
- I created a table of animals and their list prices. Separating Rabbits, Sheep and Wolves gives you much more flexibility when, say, the price of wolves doubles and sheep become less expensive. It's generally better to keep data elements separate anyway, for the sake of flexibility and power in the whole spreadsheet.
- I created a single table, named "DogDiscount" for the VLOOKUP that retrieves the discount based on quantity. You'll notice that on the order side, you just enter a quantity (not a range; the range part is taken care of in the VLOOKUP)
- I use INDIRECT to pull together the name of the table "DogDiscount" in the VLOOKUP formula. Here's how that formula reads: =VLOOKUP(I4,INDIRECT(TEXTJOIN(,1,I2,"Discount")),2,1) This enables you to create separate discount tables for CatDiscount, WolfDiscount, etc, and still use the same formula.
- All you have to do is complete the two yellow background cells, both of which use data validation to restrict the answers you can give to those that are acceptable. (Except I haven't created all the other discount tables: that's your assignment.)
Have fun!
