Dragging Index Formula down rows and changing the return column

[dreen]

I have a formula that I am using (after you paste it press "Enter+Ctrl+Shift" to run it as it has an array inside of it):

=INDEX('[Database_IRR 200-2S.xlsm]HE 171'!$F:$F,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]HE 171'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]HE 171'!$A:$A),""),1))

For the next cell below it (next row below), when I drag down the formula, I want the first part of the formula to change from '[Database_IRR 200-2S.xlsm]HE 171'!$F:$F to '[Database_IRR 200-2S.xlsm]HE 171'!$G:$G

The formula should look like this in the next row below after the above change is made (the bold is the change I want):

=INDEX('[Database_IRR 200-2S.xlsm]HE 171'!$G:$G,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]HE 171'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]HE 171'!$A:$A),""),1))

I have also posted this on: Dragging Index Formula down rows and changing the return column

 

[RoryA]

Change the first range to include all the columns you want, then add in the column argument for INDEX using ROWS, like this:

=INDEX('[Database_IRR 200-2S.xlsm]HE 171'!$F:$AZ,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]HE 171'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]HE 171'!$A:$A),""),1),ROWS($A$1:$A1))

 

[dreen]

Hello Rory,

I used this formula and it worked great (thank you for that!). I am also trying to use it with another sheet where I need it to start the index in the third row and below (basically ignore anything in the first two rows). I tried changing the $F:$ZZ to $F$3:$ZZ but whenever I added a new entry to the "Changes" sheet, the code ends up following the absolute reference and becomes

$F$4:$ZZ.​

Here is the code I am using (I thought I could your solution for both of my problems) I am currently grabbing the second value that the index finds (see the "2" at the end in bold). I want the index to return the first match it finds but from rows 3 (inclusive) and downwards while ignoring the first two rows of the sheet:

=INDEX('[Database_IRR 200-2S.xlsm]Changes'!$F:$ZZ,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]Changes'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]Changes'!$A:$A),""),2),ROWS($F$3:F3))

Please let me know if this makes sense, thank you!

 

[RoryA]

$F$3:$ZZ is not a valid reference in Excel. I suggest you limit your formula to the range you actually need, such as $F$3:$ZZ$1000.

 

[dreen]

$F$3:$ZZ is not a valid reference in Excel. I suggest you limit your formula to the range you actually need, such as $F$3:$ZZ$1000.

Thank you for your help, I managed to figure it out. I have another dilemma with a similar formula that I posted on this thread: Vlookup & Offset #Value Error

I would appreciate it if you could share your knowledge on this issue and offer a solution, thank you again!