Alternative to INDIRECT() in Excel

1
votes

I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.

Can anyone suggest doing the same using a non-volatile function ? This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.

1
excel
Are the addresses always in column C? - user10852207
@user10852207, yes always - ytoamn

1 Answers

2
votes

INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.

 =MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
 'if always in column C then the range can be tightened.
 =MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)