ARRAYS Part 3 - DATA VALIDATION and TEXT

Introduction

In the last post we looked at incorporating the functionality of the SUMPRODUCT function into a multi-criteria INDEX and MATCH look-up array formula. With our source data, we are counting text values (species names); this is fine if there are no spelling errors or unknown text in the source data. In this post, we will use data validation to eliminate the possibility of errors, and then look at how to display the result of the look-up array formula in a line of text.

Dynamic Data Validation

Data validation allows us to create a drop-down list of options, and therefore ensures that there can be no errors or free text entered in to the species column of the Garden Bird Tracker so that our tables calculate properly:

We will use the same technique to provide the criteria for our array look-up formula, and ensure that we can add more species to the list at any time without having to alter the source range for the list. 

To create a dynamic list that can be added to without re-defining the source of the drop-down, I've converted the list in my DATA sheet to an Excel table:

I've then named the table TBLSPECIES:

By default, Excel will name tables in sequence as Table 1, Table 2, etc. Giving the table a name that reflects its content makes referencing the correct table in formulas and functions a lot simpler.

I've then created a second table for M/F and named this TBLMF:

In the example below, I've applied a data validation rule in the cell to the right of SPECIES(highlighted in green), allowing selection from a List, and then used an INDIRECT function to set the source as a table, rather than a range of cells. The INDIRECT function is used to create a reference to range defined as a string of text - in this example the text is the name of our table, which has a dynamic range. The table range will expand and reduce automatically, so this reference will always include all the options in the table:

I've applied the same rule in the cell next to M/F, this time using the source reference TBLMF.

Array Formula Look-up

Using the same array formula that we developed in the last post, and changing the criteria references to the cells that contain the drop down-lists, we now have a two-click look-up to find the weight by species and male/female match:

Text

The final thing to look at is how we display the result of the formula. By inserting strings of text, an additional reference and an IF function into the array formula, the output can be displayed in a much more visually pleasing way:

The formula in the example above is in cell F5, and is entered as an array formula. I've then merged the output across cells F5:L5 and applied some text and background formatting.

Note that the text is surrounded by quotation marks("?") and that formula elements, references and text is joined with an ampersand (&).  This can also be accomplished with the CONCATENATE function (or  the CONCAT function in later versions of Excel), but  the formula will be significantly longer.

I hope you enjoyed this post - let me know if you have any questions or need any help.

In the next post, an introduction to dynamic array formulas!