# How to return filtered results without using a filter

#### 288enzo

##### Board Regular
Hello - I have one sheet (sheet2) with data, on another sheet (sheet1) I would like to return filtered results of the data on sheet2. I'm working with Pro 2016.

Without a filter or pivot table.

Book1.xlsx
ABC
2Course #
3YES/NO1
4email1No
5email2Yes
6email3No
7email4Yes
8email5No
9email6Yes
10email7No
11email8No
12email9No
13email10No
Sheet2
Cell Formulas
RangeFormula
B4:B13B4=IF(COUNTIFS(AnnualTrackerData!L\$2:L\$20,A4,AnnualTrackerData!B\$2:B\$20,C\$3),"Yes","No")
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5

Sheet1 would return a list from Sheet2 A:A if B=Yes.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### Well-known Member
Hi 288Enzo,

This should work

Book1
A
1Email
2email2
3email4
4email6
5
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=IFERROR(INDEX(Sheet2!\$A\$4:\$A\$9999,AGGREGATE(15,6,ROW(Sheet2!\$A\$4:\$A\$9999)-ROW(Sheet2!\$A\$3)/(Sheet2!\$B\$4:\$B\$9999="Yes"),ROW()-ROW(\$A\$1))),"")

#### 288enzo

##### Board Regular
Hi 288Enzo,

This should work

Book1
A
1Email
2email2
3email4
4email6
5
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=IFERROR(INDEX(Sheet2!\$A\$4:\$A\$9999,AGGREGATE(15,6,ROW(Sheet2!\$A\$4:\$A\$9999)-ROW(Sheet2!\$A\$3)/(Sheet2!\$B\$4:\$B\$9999="Yes"),ROW()-ROW(\$A\$1))),"")
Thanks, that worked. I had seen that formula before but didn't understand '15,6,ROW'. Can you explain that please?

##### Well-known Member
You're welcome.

Let me split it into its component parts.
AGGREGATE(15,6,ROW(Sheet2!\$A\$4:\$A\$9999)-ROW(Sheet2!\$A\$3)
/
(Sheet2!\$B\$4:\$B\$9999="Yes")
,ROW()-ROW(\$A\$1)

AGGREGATE has many options. 15 is the SMALL option and 6 says ignore errors.
You can use the SMALL option to look for the kth smallest in a column of numbers but here I'm using the ROW(Sheet2!\$A\$4:\$A\$9999) to give me row numbers 4 to 9999 and I subtract ROW(Sheet2!\$A\$3) to give the correct index for INDEX(Sheet2!\$A\$4:\$A\$9999.

The (Sheet2!\$B\$4:\$B\$9999="Yes") will return a logical 1 when it finds a "Yes" in column B or logical 0 if not. That divided into the row numbers I'm searching will give a #DIV/0 error if logical zero which the 6 option ignores. If it's a logical 1 then the row divided by 1 returns the row number which the INDEX then selects.

The last option of AGGREGATE is the kth value so as we're using the SMALL option then 1 returns the lowest value (row), 2 returns the second lowest row, etc. So ROW()-ROW(\$A\$1) starting in row 2 is 2-1 so I get the first, then in the next row 3-1 gives me the second, etc.

#### 288enzo

##### Board Regular
You're welcome.

Let me split it into its component parts.
AGGREGATE(15,6,ROW(Sheet2!\$A\$4:\$A\$9999)-ROW(Sheet2!\$A\$3)
/
(Sheet2!\$B\$4:\$B\$9999="Yes")
,ROW()-ROW(\$A\$1)

AGGREGATE has many options. 15 is the SMALL option and 6 says ignore errors.
You can use the SMALL option to look for the kth smallest in a column of numbers but here I'm using the ROW(Sheet2!\$A\$4:\$A\$9999) to give me row numbers 4 to 9999 and I subtract ROW(Sheet2!\$A\$3) to give the correct index for INDEX(Sheet2!\$A\$4:\$A\$9999.

The (Sheet2!\$B\$4:\$B\$9999="Yes") will return a logical 1 when it finds a "Yes" in column B or logical 0 if not. That divided into the row numbers I'm searching will give a #DIV/0 error if logical zero which the 6 option ignores. If it's a logical 1 then the row divided by 1 returns the row number which the INDEX then selects.

The last option of AGGREGATE is the kth value so as we're using the SMALL option then 1 returns the lowest value (row), 2 returns the second lowest row, etc. So ROW()-ROW(\$A\$1) starting in row 2 is 2-1 so I get the first, then in the next row 3-1 gives me the second, etc.
Thank you very much for taking the time to explain.

Replies
13
Views
132
Replies
1
Views
245
Replies
5
Views
30
Replies
5
Views
68
Replies
3
Views
71

1,129,787
Messages
5,638,312
Members
417,020
Latest member
MSVII

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

### Which adblocker are you using?

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

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