Formula - Auto Fill cell dependent on variables input in multiple cells

MichaelD2480 

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.

1. 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.
2. 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)
3. 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.
4. 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!