Referencing Range names in Excel formula’s (INDIRECT function)

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).

excelindirectfunction

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:

=INDIRECT(C13&C14)

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:

=INDIRECT(“Jumpers”&”Large”)

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.

We hope you found this useful and/or interesting. If so please like, share or comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s