2

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Ilana
  • 23
  • 3

2 Answers2

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
1

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.

        Structured Table Lookup Multiple

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.

Community
  • 1
  • 1