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

[MichaelD2480]

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

[#2]

@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!

[#3]

@MichaelD2480 

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.

[#4]

Thank you so much @mathetes. I'll try working from this.

Appreciate your help!

[#5]

@MichaelD2480

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.

[#6]

@mathetes 

Im struggling. I dont quite understand how I should be trying to link my products(animals) to a respective discount table. I do see in the example you've provided that the formula accounts for this, but I am having trouble understanding where/how. I think this is where I am getting caught up.

This is the formula I have in my real scenario:

=VLOOKUP(D5,INDIRECT(TEXTJOIN(,1,B5,A24,A49,A73,A90)),2,1)

D5 = Quantity

B5 = Product type

A24, A49, A73, A90 = each is a cell with the name of a table that I have created for the respective quantities and their discounts.

[#7]

@MichaelD2480 

You'll have to tell me more before I can "decode" what's not working. Or--better yet--can you post a copy of your file on OneDrive or GoogleDrive and grant me access to it?

Absent that:

What are the choices in B5? You say it's "Product Type," but I don't know what that means? Is it where you indicate Dog, Cat, etc?

And if A24, A49, A73, A90 are the full names of the various tables that's the problem.

The whole point of the formula I created is that it creates the name of a table, one at a time, joining "Dog" and "Discount" to result in the lookup formula going to the DogDiscount table. Period. Or, when "Cat" has been selected in the animal type, that TEXTJOIN creates the name "CatDiscount" as the reference for that table. But you don't pack references to the names of all the tables into the formula.

Here, let me break down my formula for you.

=VLOOKUP(I4,INDIRECT(TEXTJOIN(,1,I2,"Discount")),2,1)

• the TEXTJOIN function, after the ",1," part. simply joins whatever is in cell I2 (Dog or Cat or whatever) with the text "Discount", to result in the table name "DogDiscount" or "CatDiscount" or "______Discount"
• INDIRECT uses that as the array name to be used in the VLOOKUP formula
• VLOOKUP looks at I4 (quantity) in the table array named "DogDiscount", takes the second column of the closest number lower than the quantity being ordered.

(I should note that TEXTJOIN is a relatively new function so you do need Excel 2019 or newer.)

One thing you could do, rather than work with a completed formula and find it failing, is to build it from the inside out. In this case, just use TEXTJOIN and the internal variables needed to produce a single table name, and see what it produces. Then add the outer functions......  But it's pretty clear to me that you're packing too much into TEXTJOIN.

[#8]

@mathetes Thank you for this breakdown. This is really helpful for me to understand how the formula is functioning. I was able to get it to work in my spreadsheet! Now I'll just need to do some searching to find out if there is a way to assign multiple names to a single table. Otherwise I will need to get working on creating about 75 discount tables. Fun! :)

[#9]

@MichaelD2480 

I'm quite sure you can have multiple names for a table. However, there may be other ways too to accomplish this. Are they 75 entirely distinct tables? I gather not. So in that case, maybe you have a table of table names, and use INDIRECT to refer to the right table name that pertains to Dogs, Kangaroos and Giraffes, rather than to construct separate table names for each animal.

I did it the other way because I thought you had only a few.

[#10]

@MichaelD2480 

A quick postscript: I see I never gave you a reference to help. you study the use of INDIRECT. Here's a link to a website that I find very helpful. Here, specifically, is a link to the page on the INDIRECT function itself.

And here, I've created an example of what I referred to in the last posting. A table of table names, so now the five animals in this sample can refer to one of two different tables of discounts. I renamed those, creatively, to DiscountOne and DiscountTwo. And the INDIRECT function uses VLOOKUP to get the appropriate table name (NOT to create it, just to look it up), and does that nested within the VLOOKUP that then gets the discount based on volume.