Return multiple items from a table

Question

I have an Excel file with two sheets. In "Sheet 1" I have data that is formatted as a table. It has three columns:

MyData[Name]         MyData[Month]         MyData[sales]

On "Sheet 2" I want to select multiple items from MyData that match a criterion (that MyData[Name] = John).

I have found this great video that explains how to do this, with an array formula that combines INDEX, SMALL, ROWS and IFs. The problem is that in the video they use regular ranges with hard-coded row numbers.

Is there a way to do this selection, but to use column names instead of the hard-coded ranges?

Answer 1

Yes, though I am not sure about relative referencing using structured references, so offer two array formulae, assuming your selection criterion (eg John) is in A1 of your second sheet:

In B1:

=IF(ROWS(A$1:A1)<=COUNTIF(MyData[[#All],[Name]],$A$1),INDEX(MyData[[#All],[Month]],SMALL(IF(MyData[[#All],[Name]]=$A$1,ROW(MyData[[#All],[Name]])-ROW(MyData[[#Headers],[Name]])+1),ROWS(B$1:B1))),"")  

In C1:

 =IF(ROWS(B$1:B1)<=COUNTIF(MyData[[#All],[Name]],$A$1),INDEX(MyData[[#All],[sales]],SMALL(IF(MyData[[#All],[Name]]=$A$1,ROW(MyData[[#All],[Name]])-ROW(MyData[[#Headers],[Name]])+1),ROWS(C$1:C1))),"")  

both entered in second sheet with Ctrl+Shift+Enter and copied down until no values are returned.