Excel Cookbook

Chapter 7. Array, Logic, and Lookup Functions

The recipes in this chapter cover three main areas that many types of spreadsheets require:

• Manipulating arrays or ranges as part of a formula

• Performing logical tests

• Looking up values by name and index

The chapter includes filtering, sorting, and combining arrays using functions instead of menu commands; trapping and handling errors; choosing values to return based on a logical test or matching value; and using XLOOKUP, INDEX, and MATCH to look up values. It also covers ways of working with the INDIRECT and OFFSET functions to create dynamic references to cells and ranges.

7.1 Getting Unique Values

Problem

You have an array or range and want to retrieve a list of its unique values or the ones that appear exactly once.

Solution

Suppose A2:A6 lists customer first names, B2:B6 lists their last names, and you want to use this data to return two lists: one containing the unique names and another containing names that appear only once.

If you’re using Excel 2021 or Excel 365, you can solve this problem using the UNIQUE function to return a dynamic array of values. Generally, you use the formula =UNIQUE(array, by_column, exactly_once), where array is the range or array whose values you want to examine, by_column (optional) specifies whether you want to return unique rows or columns (omit it to return unique rows or set it to TRUE to return unique columns), and exactly_once (optional) specifies whether you want to return values ...