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
Post a Comment