be able to drag this formula down

[mattrx731]

I would like to be able to drag this formula down about 20 rows and have only the red S increase as its dragged down. So the S would change to a T in the row below it, then U, etc.

INDEX(Rentals!$S$2:$S$200,MATCH($D$4,Rentals!$BC$2:$BC$200,0),1)

Thanks

 

[AlphaFrog]

INDEX(OFFSET(Rentals!$S$2:$S$200,,ROW(A1)-1),MATCH($D$4,Rentals!$BC$2:$BC$200,0),1)

OFFSET Function

 

[T. Valko]

I would like to be able to drag this formula down about 20 rows and have only the red S increase as its dragged down. So the S would change to a T in the row below it, then U, etc.

INDEX(Rentals!$S$2:$S$200,MATCH($D$4,Rentals!$BC$2:$BC$200,0),1)

Thanks

Index the ENTIRE range and then use a column incrementer. Something like this...

Let's assume you want to enter the first formula in cell A2 and then drag copy down.

Entered in A2:

=INDEX(Rentals!$S$2:$AB$200,MATCH($D$4,Rentals!$BC$2:$BC$200,0),ROWS(A$2:A2))

So, the formula will evaluate to:

A2: INDEX(Rentals!$S$2:$S$200
A3: INDEX(Rentals!$T$2:$T$200
A4: INDEX(Rentals!$U$2:$U$200
A5: INDEX(Rentals!$V$2:$V$200
A6: etc
A7: etc

 

[mattrx731]

INDEX(OFFSET(Rentals!$S$2:$S$200,,ROW(A1)-1),MATCH($D$4,Rentals!$BC$2:$BC$200,0),1)

Thank You, this worked for me.