By using the INDIRECT function in Excel, its possible to reference Excel Range Names based on other cell values.
There are many different use cases for this, but I’ll use a relatively simple one here to demonstrate how INDIRECT can be used. Hopefully this will provide you with enough information to apply this function to your situation (if relevant).
In the above example we see a simple list of values in Excel (Item, Size and Quantity). Each cell containing a Quantity value has been given a range name (JumpersSmall, JumpersMedium, JumpersLarge, TshirtsSmall…..etc.).
Below the list of values we see two cells with red borders. One is used for an Item selection and the other for a Size selection.
Below the red bordered cells with see a Result cell. The formula in this cell is:
In this example the INDIRECT function is being used to return a Quantity value based on the Item and Size selected in the red bordered cells. In the example Jumpers has been selected as the Item and Large as the size. So the above formula is now effectively:
This formula will return the cell value from the Range named JumpersLarge, so here a value of 312 is returned.
The same result could be achieved using other methods, including nested IF’s; however, this method results in cleaner and simpler formula’s. Sure! there’s a bit of extra effort required upfront, but as is usually the case this extra effort pays off in the end.