Forum Discussion
Formula - Auto Fill cell dependent on variables input in multiple cells
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.
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.
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.
- 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! 🙂