Aug 02 2022 08:00 AM - edited Aug 02 2022 08:01 AM
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
Aug 02 2022 09:14 AM - edited Aug 02 2022 09:15 AM
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.
Have fun!
Aug 02 2022 09:25 AM
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.
Aug 02 2022 11:26 AM
Aug 02 2022 05:32 PM - edited Aug 02 2022 05:33 PM
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.
Aug 04 2022 12:52 PM
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.
Aug 04 2022 02:01 PM - edited Aug 04 2022 02:02 PM
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)
(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.
Aug 09 2022 02:27 PM
Aug 09 2022 03:08 PM - edited Aug 09 2022 04:28 PM
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.
Aug 09 2022 07:36 PM
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.