INDEX SMALL ROW array function

Hi everyone,

I am having trouble getting the INDEX SMALL ROW combination to work for aggregating data from a larger set of data.

Please see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.

Many thanks for your help,
ACurtis802

If you revise your approach such that instead of FALSE you generate an incrementing number you can dispense with poor performing arrays altogether and revert to a basic index match which will perform far (far) better.

If you can tell me the logic that determines as to whether or not the cells should generate FALSE or not in the first instance, yes.

OK let's assume you want to keep your FALSE separate ... let's use an adjacent cell - it will help based on the initial sample you provided anyway...

First if we assume per your latest formula that you don't have a header row then let's delete rows 1 & 2 from your sample file such that first row of data (3) becomes row 1

Please Login or Register  to view this content.

Your desired results can then use this index number to retrieve data

Please Login or Register  to view this content.

obviously it makes sense to store the MAX in a separate cell rather than repeatedly calling it... if you have lots of data it even makes sense to store the MATCH result in a dummy column

I think the key is that it's not the number of functions that are of primary concern rather it is the nature of functions - a handful of arrays can still be much slower than a significant number more non-array formulae combined.

EDIT: Apologies: revised approach to negate need for exact MATCH