INDEX MATCH using "configuration" strings in separate cells

Question

My attempt to use a value in a particular cell to configure the index argument has not been panning out well to this point.

I have attempted to create a configuration sheet to define the index ranges used throughout my working data sheet with a formula like this:

INDEX(Config!R1C2,MATCH(RC1,Config!R1C3,0),Config!R1C4)

In the Config!R1C2 cell as an example I'd put something like RawData!R1C1:R100000C100 for the index range, then in Config!R1C3 I'd define the match lookup range with something like RawData!R1C5:R100000C5.

This is an attempt at allowing me to populate a report using Index Match, while avoiding having to reconfiguring every formula each time my raw data's format changes (which changes often and I have zero control over). With this configuration sheet I'd hopefully be able to just redefine the index range, and match range in one cell.

Two useful techniques for creating a dynamic named range are the OFFSET-COUNT approach and the INDEX-COUNT approach. Both can be used to create dynamic ranges that adjust in row and/or column count based on the size of data. With that range defined, you can do INDEX-MATCH or whatever else with the dynamic range. OFFSET: excel-easy.com/examples/dynamic-named-range.html . INDEX: blog.contextures.com/archives/2011/02/25/… — Byron Wall
If you want to use your existing approach and are typing references into those Config! cells, you will likely need to use INDIRECT to convert the string to a proper reference. support.office.com/en-sg/article/… — Byron Wall

teylyn teylyn · Accepted Answer · 2015-04-14T21:29:28

In your formula you would need to wrap the cell reference into an Indirect() function in order to evaluate the contents of cell Config!R1C2 as a range.

Indirect is volatile, though and may slow down your workbook.

Consider using named formulas (named ranges) instead. Create three range names (Formula ribbon > Name Manager > New ). Name the ranges and let them refer to the following

IndexRange = RawData!R1C1:R100000C100
RowRange = RawData!R1C5:R100000C5
ColumnRange = Config!R1C4

Then you can adjust your formula to this:

INDEX(IndexRange,MATCH(RC1,RowRange,0),ColumnRange)

You can always edit the named ranges, or you can use formulas instead of absolute references, which will make the ranges grow and shrink with the available data (avoids using thousands of empty rows and columns).

INDEX MATCH using "configuration" strings in separate cells

1 Answers