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?

what is your data sample like? give an example of what you have tried — Marcel
– Marcel, Commented Jul 2, 2015 at 19:30

pnuts · Accepted Answer · 2015-07-02 22:09:05Z

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.

answered Jul 2, 2015 at 22:09

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

You generally do not require all of the structured table references that come with point and select on table objects as you build a formula. Much like the verbose code produced by the macro recorder, the auto-generated table range references are meant to handle a variety of situations and some pieces may not be necessary or desired.

The standard formulas in G4:H4 are,

=IFERROR(INDEX(MyData[Month], AGGREGATE(15, 6, ROW(MyData)-ROW(MyData[#Headers])/(MyData[Name]=$F$4), ROW(1:1))), "")
=IFERROR(INDEX(MyData[Sales], AGGREGATE(15, 6, ROW(MyData)-ROW(MyData[#Headers])/(MyData[Name]=$F$4), ROW(1:1))), "")

This uses the AGGREGATE¹ function in its SMALL form to force an error on any non-matching rows and subsequently ignore the errors when retrieving the multiple matching items.

¹_{The AGGREGATE function was introduced with Excel 2010. It is not available in previous versions.}

Collectives™ on Stack Overflow

Return multiple items from a table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related