How to return filtered results without using a filter

[288enzo]

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
	A	B	C
2			Course #
3		YES/NO	1
4	email1	No
5	email2	Yes
6	email3	No
7	email4	Yes
8	email5	No
9	email6	Yes
10	email7	No
11	email8	No
12	email9	No
13	email10	No
Sheet2

Cell Formulas
Range	Formula
B4:B13	B4	=IF(COUNTIFS(AnnualTrackerData!L$2:L$20,A4,AnnualTrackerData!B$2:B$20,C$3),"Yes","No")

Cells with Data Validation
Cell	Allow	Criteria
C3	List	1,2,3,4,5

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

 

[Toadstool]

Hi 288Enzo,

This should work

Book1
	A
1	Email
2	email2
3	email4
4	email6
5
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=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]

Hi 288Enzo,

This should work

Book1
	A
1	Email
2	email2
3	email4
4	email6
5
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=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?

 

[Toadstool]

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]

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.