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