CODEWITHSUNDEEP
excelexcel-formula
MWPDX
MWPDX

Reputation: 21

Define Named Range with Array Formula

I have a defined table of courses, dates, and instructors. When in Excel, I can obviously filter the table to only show one course. I can also create another worksheet and use INDEX/MATCH array formula to show a filtered table. However, I am trying to define a named range that would output the two dimensional array of the filtered table.

Course             Date    Instructor
Winemaking         7/3     Fred
Paper Airplanes    7/16    Bill
Advanced Kazoo     7/22    Sally
Winemaking         8/6     Harry
Cooking 101-Toast  8/19    Beth
Advanced Kazoo     8/24    Xavier
Winemaking         9/14    Fred
Paper Airplanes    9/25    Lilly

I would like a named range of, say CourseWine, to equal:

Winemaking         7/3     Fred
Winemaking         8/6     Harry
Winemaking         9/14    Fred

I am hoping for a named range because it:

  1. Is incredibly easy to show with a SharePoint Excel Web Part
  2. Would not require any updating of a helper table
  3. Is less likely to get mucked up by other users editing the spreadsheet

I have tried a couple of combinations (one from Excel - Array formula as name range not working) but every time the named range is only the first row of my desired filtered output.

=INDEX(Table,MATCH(1,(Table[Course]="Winemaking")*(Table[Date]>TODAY()),0),0)

=INDEX(Table,SMALL(IF(Table[Course]="Winemaking",ROW(Table[Course])-MIN(ROW(Table[Course]))+1),ROW(INDIRECT("1:"&COUNTIF(Table[Course],"Winemaking")))),0)

Is there a formula that I can define a named range with that will return the two dimensional array? I could also achieve this with a pivot table, but does not fully prevent someone from changing the table and messing up the view from SharePoint... I cannot resort to sorting the list by course instead of by date. I would also like to avoid VBA. And is this even possible?

Upvotes: 0

Views: 6657

Answers (1)

Excel Hero
Excel Hero

Reputation: 14754

To return multiple look-up values from an array formula with INDEX(), you locate the values with SMALL() instead of MATCH().

For your circumstance, you can use this array formula:

=IFERROR(INDEX(Table,SMALL(IF(("Winemaking"=Table[Course])*(Table[Date]>TODAY()),ROW(Table[Course])-MIN(ROW(Table[Course]))+1, ""),ROW(A1)),{1,2,3}),"")

Notes:

  1. The formula should be entered for all cells of the output's first row, then copied row-by-row.

  2. This means that if you want the output in the range D15:F20

    ...then you will select D15:F15, and enter the formula once for that range with Ctrl-Shift-Enter.

    ...then you will copy the first entered range to D16:F20.

  3. It is important that the A1 near the end be entered as a Relative Reference.

  4. The literal array at the very end ensures the appropriate colums of the table are returned.

  5. The whole formula is wrapped in IFERROR() to remove errors showing in the output when the number of rows of filtered results is less than the number of rows that you have entered the formula.

Upvotes: 1

Related Questions