Define Range From Cell Value - in Array Formula

[ipperz]

Hello!

I am having trouble slimming down an array formula and am looking for suggestions. The below is part of a Index/Match array formula. What I want to do is make the Criteria portion of COUNTIF (the end range of 'Sheet2'!T$2:$T$250) based off the cell value in A1.

If A1 = 12, then it should be T$2:$T$12, instead of $250.

{=MATCH(0,COUNTIF($A3,'Sheet2'!T$2:$T$250),0)}

I've tried adding Indirect and Concatenate, as below, but it isn't working.

{=MATCH(0,COUNTIF($A$3,'Sheet2'!T$2:INDIRECT(CONCATENATE("T",A1))),0)}

Thank you for your insight!

 

[pgc01]

Hi

The start and end of the range must be on the same worksheet.

Try your formula with:

=MATCH(0,COUNTIF($A$3,Sheet2!T$2:INDIRECT(CONCATENATE("'Sheet2'!T",A1))),0)

this should work, but it's better not to use Indirect(). You can use instead:

=MATCH(0,COUNTIF($A$3,Sheet2!T$2:INDEX(Sheet2!T:T,A1)),0)

Remark: you could also use just 1 function (also array entered):

=MATCH(FALSE,Sheet2!T$2:INDEX(Sheet2!T:T,A1)=A3,0)