Dynamic Array Functions Example

Introduction

I just answered a question that asked, in Excel, how can we find more than one text with certain criteria from sheet 2 to appear in sheet 1? That's not the most lucid question ever asked but you will see my answer as this page unfolds.

I used dynamic array functions to create a succinct answer to this question.

Firstly, I generated a data set of 50 names with values on Sheet 2 of an Excel file, the first 20 of which are:

Sheet 1 contains my answer and here it is:

I said, in cell B5, set a filter value of 225

In cell A7 I entered this dynamic array formula to look for values greater than B5: 

=FILTER(Sheet2!A2:B51,Sheet2!B2:B51>B5,"None")

This formula says look at the input table on Sheet 2, A2:B51 and find any value in B2:B51 that is greater than the value in Sheet 1 cell B5 and record every instance where that is true, starting in cell Sheet 1, A7 … it creates the full list automatically.

Other Matters

I could have asked FILTER() to find >B5, =B5, <B5, <=B5, >=B5, <>B5, of course

I can change the value in cell B5 at any time and this formula will provide you with the correct solution. Even if I enter an inappropriate value in B5, say, 9999:

Which means that the FILTER() functions allows us to tell us when there is nothing to report.

If it is of interest, I then created the formula in Sheet 1 cell D7 to return any result from the FILTER() function that is unique. That is, someone who has at least one entry in Sheet 2 A2:B51 … that might be of interest but note, if I had used this formula, =UNIQUE(FILTER(Sheet2!A2:B51,Sheet2!B2:B51>B5,"None")), I would have got all unique people AND all unique values. That is, for example, Luca Wise appears three times in this final list because her value was >225 three times with three different values.

Download my Excel file so that you can follow along with me.

Duncan Williamson

12th January 2021