excel - INDEX MATCH using "configuration" strings in separate cells -


my attempt use value in particular cell configure index argument has not been panning out point.

i have attempted create configuration sheet define index ranges used throughout working data sheet formula this:

index(config!r1c2,match(rc1,config!r1c3,0),config!r1c4)

in config!r1c2 cell example i'd put rawdata!r1c1:r100000c100 index range, in config!r1c3 i'd define match lookup range rawdata!r1c5:r100000c5.

this attempt @ allowing me populate report using index match, while avoiding having reconfiguring every formula each time raw data's format changes (which changes , have 0 control over). configuration sheet i'd able redefine index range, , match range in 1 cell.

in formula need wrap cell reference indirect() function in order evaluate contents of cell config!r1c2 range.

indirect volatile, though , may slow down workbook.

consider using named formulas (named ranges) instead. create 3 range names (formula ribbon > name manager > new ). name ranges , let them refer following

indexrange = rawdata!r1c1:r100000c100 rowrange = rawdata!r1c5:r100000c5 columnrange = config!r1c4 

then can adjust formula this:

index(indexrange,match(rc1,rowrange,0),columnrange)

you can edit named ranges, or can use formulas instead of absolute references, make ranges grow , shrink available data (avoids using thousands of empty rows , columns).


Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -