Regarding usage of ADDRESS with INDEX and MATCH

[ice00monster]

Say for example I was able to create a range using address:

=ADDRESS(BF2,26)&":"&ADDRESS(BF3,26) --> this would result into $Z$384:$Z$1123

I want to use this in MATCH

=MATCH(BC7,$Z$384:$Z$1123,-1) ---> this would work

=MATCH(BC7,ADDRESS(BF2,26)&":"&ADDRESS(BF3,26),-1) ---> this would NOT work

how can I make the second case work?

[JohnTopley]

Try


=MATCH(BC7,INDIRECT(ADDRESS(BF2,26)&":"&ADDRESS(BF3,26)),-1)

[Pepe Le Mokko]

Perhaps =MATCH(BC7,indirect(ADDRESS(BF2,26)&":"&ADDRESS(BF3,26)),-1)
(untested)

[AliGW]

You could try this:

=MATCH(BC7,INDIRECT(ADDRESS(BF2,26)&":"&ADDRESS(BF3,26)),-1)