Array formula to return list of values that match multiple criteria

**TFiske** · 03-30-2016, 11:24 AM

Hi all,

I've been wrestling with a difficult array formula problem. I have a large array of TRUE/FALSE values that designate if a particular area will be serviced on a particular ISO Week Number. I need an array formula that will return a list of areas to be serviced (indicated by a cell value of "TRUE") when the user enters a given ISO Week Number.

Untitled picture.png

I have attached a copy of the spreadsheet. I have tried several versions of an INDEX(MATCH) and have come close but I only get a list of a single area that matches the criteria, not the whole list.
Looking forward to your feedback.

-T

**JeteMc** · 03-30-2016, 11:54 AM

Paste this array formula* into Sheet2!F5:

Please Login or Register  to view this content.

*After pasting the formula into the cell:
1) Press the F2 key,
2) Press the Ctrl, Shift and Enter keys simultaneously,
3) Double click on the fill handle to send the formula down column F (or drag as far as desired).
Let me know if you have any questions.

**TFiske** · 03-30-2016, 12:15 PM

This is exactly what I was looking for. Thank you. If you might explain the syntax to me that would be wonderful. It had not occurred to me that i would need multiplication and division functions to define the row and column.

**JeteMc** · 03-30-2016, 12:40 PM

As you stated the math in the array argument of the SMALL function finds the column number for the Index function. The best way to see how this works is to run "Evaluate Formula" from the formula tab. You'll see that all of the column numbers are restricted to (divided by) those that are on the row with week #13 AND (multiplied by) have a value of TRUE. Those values not in the week 13 row or having a value of FALSE will produce a zero in the denominator causing a #DIV/0 which is caught by the interior IFERROR.
The small function then picks the actual column number based on the value of the ROW function in that particular cell. If there is not a column heading returned by the INDEX in that cell the leading IFERROR traps the ensuing #Value. Hope that explanation is understandable, let me know if not.
Also want to say; Your Welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

**TFiske** · 03-30-2016, 01:57 PM

No, that makes sense. Thank you again!

Array formula to return list of values that match multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Array formula to return list of values that match multiple criteria

Re: Array formula to return list of values that match multiple criteria

Re: Array formula to return list of values that match multiple criteria

Re: Array formula to return list of values that match multiple criteria

Re: Array formula to return list of values that match multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Array Match and Return value upon multiple array match criteria

INDEX / MATCH formula to return multiple values - linked to several criteria (buttons)

Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formula

Array formula to return multiple values based on various (OR) criteria

Bookmarks

Bookmarks

Posting Permissions