Lookup and Dynamic Array Functions

In this chapter, we’ll discuss two types of worksheet functions that will literally transform your workbooks. Lookup functions have long allowed us to create formulas that can retrieve data from elsewhere in a spreadsheet without requiring us to connect directly to the individual cells. Dynamic array functions are a quantum leap forward, enabling us to create single formulas that can return results to multiple cells. The new SORT, FILTER, and UNIQUE functions enable us to automate tasks that, previously, always had to be carried out by hand. The new XLOOKUP function is both a lookup function and a dynamic array function that is a modern replacement for both VLOOKUP and MATCH/INDEX. As you’ll discover, dynamic array functions can vastly improve spreadsheet integrity by creating self-resizing schedules, such as a dynamic amortization schedule. Amortization schedules are used to document the interest, principal, and running balance over...

Technical requirements

Dynamic array functions are available in Microsoft 365, Excel 2021, Excel for Web, Excel for iPad, Excel for iPhone, and Excel for Android. These functions return #NAME? in Excel 2019 and earlier, the use of the Spilled Range Operator (#) could result in other errors too, such as #VALUE.

An example workbook that contains all of the formulas used in this chapter can be found on GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter10.

Now that we’ve taken a look at the prerequisites for this chapter, let’s begin with the VLOOKUP and INDEX/MATCH functions, which are often used to look up information in spreadsheets, and see how XLOOKUP enables you to do so much more than you might have previously imagined.

The VLOOKUP function

Lookup functions in Excel enable you to retrieve data from a list. The =VLOOKUP(G3,B3:E12,3,FALSE) formula in cell H3 of the VLOOKUP and IFNA worksheet of this chapter’s example workbook returns 94 as the length of Ninety Mile Beach in miles, as shown in Figure 10.1. Later in this section, I’ll discuss why VLOOKUP only returns a single match:

Figure 10.1 – The VLOOKUP and IFNA functions

VLOOKUP has four arguments:

• Lookup_value – This is the value that you’re searching for within a list, which, in this case, is G3. VLOOKUP looks down the first column of the table array for this value.
• Table_array – This refers to the cell coordinates of the list that you wish to search; in this case, they are B3:E12.
• Col_index_num – This is the column position within table_array that you wish to return data from; in this case, 3 represents the third column so that we can look up the length...

The IFNA function

The =IFNA(VLOOKUP(G13,B3:E12,3,FALSE),"¯\_(ツ)_/¯") formula in cell H13 returns a whimsical emoji shrug, ¯\_(ツ)_/¯, in place of the #N/A error. In comparison, the =IFNA(VLOOKUP(G13,B3:E12,4,FALSE),"Not Found") formula in cell I14 returns a more practical message of Not Found when VLOOKUP returns #N/A.

Nuance

Sometimes, users inadvertently bump the spacebar when entering data in Excel, which can result in trailing spaces that are tricky to track down. Inconsistencies such as this cause users to assume Excel is broken or that they simply don’t understand lookup functions. When you double-click on a cell, check to see whether the cursor is positioned immediately adjacent to the last character in the cell. If not, press backspace as needed to eliminate the extra spaces, or use the TRIM function.

IFNA has two arguments:

• Value – This is a calculation that could return #N/A, such...

The MATCH function

The =MATCH(G3,B3:B12,0) formula in cell H3 of the MATCH and INDEX worksheet in Figure 10.2 returns 2 because it found Long Beach in the second row of the B3:B12 range:

Figure 10.2 – The MATCH function

The MATCH function has three arguments:

• Lookup_value – What to look for, which, in the case of cell H3, is the contents of cell G3.
• Lookup_array – This can be a row, column, or array to search; in the case of cell H3, it is the B3:B12 range.
• Match_type – This optional argument offers three choices:
  • -1 – Find the closest match that is equal to or less than lookup_value.
  • 0 – Find an exact match with lookup_value, which I specified in cell H3.
  • 1 – Find the closest match that is equal to or greater than lookup_value. This is the default value for MATCH if you don’t specify the third argument.

The =MATCH(G8,B3:B12,0) formula in cell H8 of Figure 10.2 returns #N/A because...

The SUMIF function

The =SUMIF($B3:$B12,$G3,D3:D12) formula in cell H3 of Figure 10.5 returns 149 as the sum of the miles for both instances of Ninety Mile Beach:

Figure 10.5 – The SUMIF function

The SUMIF function has three required arguments:

• Range – This refers to a row or column that you wish to search, which, in this case, is $B3:$B12.
• Criteria – This refers to a value to search for, which, in this case, is $G3.
• Sum_range – This refers to a row or column that you wish to add up values from, which, in this case, is D3:D12.

As shown in cell H8 of Figure 10.5, the =SUMIF($B3:$B12,$G8,D3:D12) formula returns 0 because the hyphenated Ninety-Mile Beach version does not appear in cells B3:B12. As we saw previously, in such situations, VLOOKUP and MATCH would return #N/A. Typically, you would not want to add both Ninety Mile Beaches together but would instead want to look up the length of one beach or the other...

The SUMIFS function

The =SUMIFS(D3:D12,$B$3:$B$12,$G13,$C$3:$C$12,$G12) formula in cell H13 of Figure 10.5 returns 55 as the distance of Ninety Mile Beach in New Zealand.

The SUMIFS function can sum data based on up to 127 criteria:

• Sum_range – This refers to a row or column that you wish to add up values from, which, in this case, is D3:D12.
• Criteria_range1 – This refers to a row or column that you wish to search, which, in this case, is $B$3:$B$12.
• Criteria – This refers to a value to search for, which, in this case, is $G13 for Ninety Mile Beach.
• Criteria_range2 – This refers to a row or column that you wish to search, which, in this case, is $C$3:$C$12.
• Criteria2 – This refers to a value to search for, which, in this case, is $G12 for New Zealand.

SUMIFS returns zero if a match cannot be found, as shown in cell H18 of Figure 10.5. Keep adding additional criteria ranges and criteria as needed when you need to add...

The XLOOKUP function

As you will see, XLOOKUP not only eliminates the frustrations I mentioned for VLOOKUP, it enables you to create a simpler formula than INDEX/MATCH and adds much more functionality. The =XLOOKUP($G3,$B3:$B12,D3:D12) formula in cell H3 of the XLOOKUP Exact Match worksheet in Figure 10.6 returns 94 as the length of Ninety Mile Beach in miles. As with VLOOKUP, XLOOKUP stops looking after it finds an initial match:

Figure 10.6 – The XLOOKUP function

Tip

XLOOKUP eliminates most, but not all, of the previous uses for INDEX/MATCH. XLOOKUP doesn’t allow you to simultaneously search down a column and across a row like INDEX with two MATCH functions allows, so it’s good to have both approaches in your repertoire.

XLOOKUP has a total of six arguments, but often, you’ll only need to enter the three required arguments:

• Lookup_value – A value that you’re searching for, such as the contents of cell...

The XMATCH function

The =XMATCH(G3,B3:B12) formula in cell H3 of Figure 10.11 returns 9 because it found Stockton Beach in the ninth row of the B3:B12 range. The =XMATCH(G8,B3:B12) formula in cell H8 returns #N/A because Short Beach does not appear within cells B3:B12. Finally, the =XMATCH(G13,B2:E2) formula in cell H13 of Figure 10.11 returns 3 because it found Miles in the third column of the B2:E2 range. As you can see, XMATCH can look down rows or across columns, just like the MATCH function:

Figure 10.11 – The XMATCH function

The XMATCH function has four arguments:

• Lookup_value – What to look for.
• Lookup_array – A row, column, or array to search.
• Match_type – This optional argument offers the same four choices as XLOOKUP:
  • 0 – Exact match, or return #N/A
  • -1 – Exact match, or return the next smaller item
  • 1 – Exact match, or return the next larger item
  • 2 – Wildcard match using *, ?, or ~
...

The UNIQUE function

The UNIQUE function gives you a self-updating means of removing duplicates from another list. The =UNIQUE(C3:C12) formula in cell G3 of Figure 10.12 returns one of each country’s names from the list in cells C3:C12, spilling the results into cell G4 and beyond as needed:

Figure 10.12 – The UNIQUE function

The UNIQUE function has three arguments:

• Array – The range of cells that you wish to remove duplicates from, which, in this case, is cells C3:C12.
• by_col – An optional argument that defaults to TRUE, which instructs Excel to remove duplicates from columns. Use FALSE if you wish to instead remove duplicates across rows. In this case, I omitted the argument.
• exactly_once – An optional argument that defaults to TRUE to remove duplicates. Use FALSE if you wish to display every item from the original range. In this case, I omitted the argument.

The =UNIQUE(C3:C12,TRUE,FALSE) formula...

The SORT function

In Figure 10.13, I clicked on cell D3 and then clicked on Sort Largest to Smallest on the Data tab of Excel’s ribbon. The =SORT(B3:E12) formula in cell G3 shows the list sorted back into alphabetical order again. In this case, the SORT function is spilling results into columns H:J and rows 4:12:

Figure 10.13 – The SORT function

The SORT function has the following arguments:

• Array – The range of cells that you wish to sort, which, in this case, is cells B3:E12.

Tip

Make sure that you don’t include your heading row in the array; otherwise, you’ll most likely sort the column headings into the body of your list.

• Sort_index – An optional column position that you wish to sort on. In this case, I omitted this argument, but I’ll show you an example later.
• Sort_order – This optional argument defaults to 1, which indicates you want to sort in ascending order, or you...

The FILTER function

The =FILTER(B3:E12,B3:B12=H2) formula in cell G6 of Figure 10.17 displays all the beaches for the name entered in cell H2, in this case, Ninety Mile Beach. Earlier in the chapter, you saw that functions such as VLOOKUP, MATCH, and XLOOKUP stop looking after finding an initial match. The FILTER function gives you a way to return multiple values from a list:

Figure 10.17 – The FILTER function

Tip

It’s best not to embed criteria within the formula itself. Using input cells such as cell H2 enables you or other users to change the criteria without having to edit the formula.

The FILTER function has two required arguments and one optional argument:

• Array – This required argument is a list composed of one or more columns that you wish to filter, which, in this case, is B3:E12.
• Include – This required argument is the cell coordinates of the column that you wish to filter on and the criteria itself, which...

The Spilled Range Operator

The average spreadsheet user spends a lot of time managing formulas in spreadsheets, particularly when new data is added to a list. The Table feature, which I discuss in Chapter 7, Automating Tasks with the Table Feature, offers one approach for eliminating this manual task. A second approach involves using the Spilled Range Operator (#) to create formulas that expand into more rows or contract into fewer rows based on changes in your data.

Nuance

You can only use the Spilled Range Operator in conjunction with formulas that reference results generated by a dynamic array function. This means the Spilled Range Operator is not available in Excel 2019 and earlier. If you use the Spilled Range Operator to reference data that is not in a dynamic array, the formula might return zero or a #VALUE! error.

The =SORT(UNIQUE(FILTER(C3:C12,D3:D12>H2))) formula in G6 of Figure 10.22 combines several concepts from this chapter to generate an alphabetical list...

The dynamic amortization table

An amortization table details how loan payments are allocated on a period-by-period basis, such as monthly. Additionally, an amortization table shows how the amount of the loan declines over time, typically down to zero or to an agreed-upon balloon payment to be made at the end. You might have encountered an amortization table in the paperwork provided along with the purchase of a car or a house. Historically, amortization tables generally required manual adjustments to accommodate different loan lengths. For instance, an amortization table for a 48-month car loan would need to have 12 more rows added if you opted for a 60-month loan instead. Dynamic array functions now enable us to create dynamic amortization tables that do not require much maintenance but instead expand or contract automatically to match the length of the loan.

Prior to the advent of dynamic array functions, you could create a macro to resize an amortization table on demand, or you...

The #SPILL! errors

As you might expect, new functionality in Excel such as dynamic array functions can result in new types of error prompts, too. In The Filter function section, I discussed the #CALC! error. In this section, I’ll discuss the #SPILL! error. The first error that we’ll look at arises when other data resides in the immediate area that a dynamic array function needs to display its results. This error can also arise when a user overwrites data that has been returned by a dynamic array function.

The #SPILL! error can appear under two different conditions:

• Cell G7 in Figure 10.24 contains the words Obstruction blocking UNIQUE. If you subsequently enter the =UNIQUE(C3:C12) formula into cell G3, Excel will return #SPILL! instead of the list of countries you’re expecting. That’s because the contents of cell G7 fall within the range that UNIQUE needs to display its results. As shown, when you click on a cell that contains #SPILL!, such as...

The RANDARRAY function

Excel has three different functions that enable you to generate random numbers. Let’s look at the two longstanding options, and then we’ll look at RANDARRAY. The =RAND() formula in cell B2 of Figure 10.25 generates a random number between 0 and 1:

Figure 10.25 – The RAND and RANDBETWEEN functions

The =RANDBETWEEN(100,500) formula in cell D2 of Figure 10.25 allows you to generate random integers between 100 and 500. RANDBETWEEN has two arguments:

• Bottom – The lowest possible integer you want to return, which, in this case, is 100
• Top – The highest possible integer you want to return, which, in this case, is 500

Nuance

All random number functions in Excel are volatile, meaning that, unlike most formulas, they recalculate every time you change any cell anywhere in the workbook. Normally, Excel only recalculates formulas when you change a value that the formula directly or indirectly...

Summary

In this chapter, you learned about the concept of dynamic array functionality in Excel. Some functions, such as UNIQUE, SORT, SORTBY, FILTER, RANDARRAY, and SEQUENCE, were written from the ground up as dynamic array functions. The XLOOKUP function is a hybrid that can work as a replacement for the VLOOKUP function. Alternatively, it can work as a modern dynamic array function returning multiple results to a block of cells or summarized within another function, such as the SUM or AVERAGE functions.

Additionally, by way of the spreadsheet that we put together in the Dynamic amortization schedule section, we saw that longstanding functions in Excel such as EOMONTH, IPMT, PPMT, and SUMIF can all take on dynamic characteristics. We used the spilled range operator (#) in conjunction with referencing a set of data returned by a dynamic array function.

Dynamic array functions empower you to streamline your formulas, as a single formula can return results to hundreds or even...