INDRECT function for range in excel

andren 0
Viewed 26

for INDIRECT function formula in detail

=INDEX('TAB1'!C4:BC4,MATCH(-BM7,'TAB1'!C64:BC64,0))

But what i want is "C64:BC64" to be dynamic > for row no. meaning "B" and "BC" is fixed but the ROW no is variable which i use match function to fix it

if i use match function it become as ref error

=INDEX('TAB1'!C4:BC4,MATCH(-BM7,INDIRECT("'"&A7&"'!C"&MATCH(C7,INDIRECT("'"&A7&"'!C4:C200"),0)&":BC"&MATCH(C7,INDIRECT("'"&A7&"'!C4:C200"),0),0)))

result is #REF

solo indirect

=INDIRECT("'"&A7&"'!C"&BF2&":C"&BF2,0)    result ok
=INDIRECT("'"&A7&"'!C"&BF2&":BC"&BF2,0)   result #REF

things pointing to same cell has no issue . when column range more than 1 it show error

need assistance for this

excel-formula excel
1 Answers

The problem is the zero as the second argument:

=INDIRECT("'"&A7&"'!C"&BF2&":BC"&BF2,0)

This means you are using references in R1C1 style, and so your reference to column C, purely by coincidence, is the only reference that would not cause an error. But column "BC" is not a valid R1C1 notation, so it fails.

Since INDIRECT() defaults to A1 style references, you want either of the two following forms:

=INDIRECT("'"&A7&"'!C"&BF2&":BC"&BF2,1)

Or omit it altogether:

=INDIRECT("'"&A7&"'!C"&BF2&":BC"&BF2)

userj5vk10
Related