Index/Match | Index/Small Formula

[chrono2483]

Hello experts,

I am looking for assistance with my formula. I am using an index/small formula in the following scenario:

I am pasting raw data into Tab A, which includes status (example: Active, Inactive), and additional rows of data.
In Tab B, is a summary table, that pulls rows of data from Tab A.

In Tab A, there are multiple rows of Active, Inactive. What I want the formula to do is return the first instance row of Active, and as I drag down the formula, it will return the next example row of Active, etc.

This is the formula I have been trying to work with:

=INDEX(Sheet4!$B$2:$B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100)-ROW(Sheet4!$F$2:$F$100)+1), 1))

where I use $B$4 as a reference cell for the status (Active, Inactive)
Sheet4 is the tab where I paste the raw data
The issue I run into is that it returns the first row, regardless of if it meets the criteria of the status being active. Hoping for assistance on where I am going wrong with my formula. Hope this is clear:
Raw Data Sample

Summary page with the above formula (as you can see it returns the first row despite the status not matching, and as I drag down the formula, it returns the same value:

 

[Eric W]

Here's your issue:

=INDEX(Sheet4!$B$2:$B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100)-ROW(Sheet4!$F$2:$F$100)+1), 1))

should be:

=INDEX(Sheet4!$B$2:$B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100)-ROW(Sheet4!$F$2)+1), ROWS(C$6:C6))

Also, please update your profile with the version of Excel you're using. This would be much easier with the FILTER function available in newer versions.

 

[kvsrinivasamurthy]

Try. ARRAY formula in B6 copied to full range.

=IFERROR(INDEX(Sheet4!B$2:B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100),""), ROWS($B$6:$B6))-ROW($F$1)),"")

Normal formula in B6 copied to full range.

=IFERROR(INDEX(Sheet4!B$2:B$100, AGGREGATE(15,6, ROW(Sheet4!$F$2:$F$100)/(Sheet4!$F$2:$F$100=$B$4),ROWS($B$6:$B6))-ROW($F$1)),"")

NOTE:
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[chrono2483]

Here's your issue:

=INDEX(Sheet4!$B$2:$B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100)-ROW(Sheet4!$F$2:$F$100)+1), 1))

should be:

=INDEX(Sheet4!$B$2:$B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100)-ROW(Sheet4!$F$2)+1), ROWS(C$6:C6))

Also, please update your profile with the version of Excel you're using. This would be much easier with the FILTER function available in newer versions.

This worked perfectly. Thank you.

 

[chrono2483]

Try. ARRAY formula in B6 copied to full range.

=IFERROR(INDEX(Sheet4!B$2:B$100, SMALL(IF(Sheet4!$F$2:$F$100=$B$4, ROW(Sheet4!$F$2:$F$100),""), ROWS($B$6:$B6))-ROW($F$1)),"")

Normal formula in B6 copied to full range.

=IFERROR(INDEX(Sheet4!B$2:B$100, AGGREGATE(15,6, ROW(Sheet4!$F$2:$F$100)/(Sheet4!$F$2:$F$100=$B$4),ROWS($B$6:$B6))-ROW($F$1)),"")

NOTE:
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

This also worked. Opted to go without the array, but good to know this works too. Thank you!

 

[Eric W]

Glad we could help!