Dragging Index Formula down rows and changing the return column

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,692
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Nov 20, 2019
Messages
52
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,692
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
$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.
 
Last edited:

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
$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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,270
Messages
5,635,203
Members
416,846
Latest member
ImGoing2needaFormula

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top