0

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?

Improve this question
3
  • This is a well written question. Your circumstance and question were crystal clear, which makes answering the question much easier. Thank you. Commented Jul 14, 2015 at 20:15
  • I updated my answer to include the date criteria (course being after today) within the formula. Commented Jul 14, 2015 at 21:30
  • My email address is in my profile. Please email me and I will send you the mock-up, that works perfectly. We'll waste gobs of time going back and forth here. Commented Jul 14, 2015 at 21:49

1 Answer 1

Reset to default
1

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.

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

3 Comments

My second attempt did use SMALL (please see the two formula examples above). Your example actually changed the output of the named range from the entire row to just a single cell "Winemaking." I might not have been clear enough, but I already know how I could enter this in another worksheet as a CSE array, but I am trying to avoid that. I was hoping to define a name with the Name Manager and thus a single formula, but it is still returning just the first row instead of the entire array.
I'm looking at my formula working right now in the mock-up example I made here to craft the answer to your question. It works. It produces the full output... all three columns. My email address is in my profile. Please email me and I will send you the mock-up. We'll waste gobs of time going back and forth here.
It is a single named formula in my mock-up. But, you will need to enter it once for the first row of output and then copy that row to the other rows of desired output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.