# Define Range From Cell Value - in Array Formula

#### ipperz

##### New Member
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.

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

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

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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### pgc01

##### MrExcel MVP
Hi

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

=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)

Replies
12
Views
256
Replies
4
Views
40
Replies
2
Views
118
Replies
3
Views
36
Replies
8
Views
105

1,109,335
Messages
5,528,096
Members
409,802
Latest member
joeino

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...