Excel XLOOKUP Function

Photo by Rui Matayoshi on Unsplash

For those using the VLOOKUP or HLOOKUP functions, here is a helpful replacement for both. XLOOKUP brings many improvements to formulas used to find results within a set of data. These include the ability to find values in columns without regard to their relative position to each other, to handle errors more predictably, to search for partial matches and to return multiple results in a dynamic array.

Usage

Example 1

All of the examples below are available to download in the XLOOKUP demo workbook. The examples use revenue and expense accounts from York University. In this fictional scenario, the institution has moved from a six to a new seven-digit account numbering system to make room for additional accounts. For staff to use during and after the transition, a tool is required to lookup account numbers. A table has been set up with legacy and new account numbers for this purpose.

With the legacy account number in column A on the far left, it is simple enough to use VLOOKUP to find the new account number in a column to the right. This is the function in B9.

=VLOOKUP(B8,Accounts1[[Legacy Account]:[New Account]],2,FALSE)

However, it is equally important to be able to look up the legacy account number given the new account number. To do this without XLOOKUP, one might use INDEX and MATCH, or set up a duplicate table of accounts but with columns A and B swapped.

With XLOOKUP neither method is necessary. Rather, one specifies the lookup and return columns, or arrays.

=XLOOKUP(B11,Accounts1[New Account],Accounts1[Legacy Account],"No match",0,1)

Further, the formula provides a predictable response in the event that the lookup value is not found.

Example 2

XLOOKUP allows a lookup of a partial match using special characters * and ?.

? represents a single character, while * takes the place of multiple characters.

In the XLOOKUP2 worksheet, the user seeks an account which includes “rate” in the account description. XLOOKUP searches the Account Description column for the first partial match and returns the new account number it finds in New Account column.

=XLOOKUP(B6,Accounts2[Account Description],Accounts2[New Account],"",2,1)

Example 3

Can XLOOKUP return a dynamic array of multiple results?

Yes, and, not yet.

The MultiResult worksheet in the demo workbook includes two demonstrations of returning multiple results. The first one uses XLOOKUP, the second uses FILTER.

In cell B5 the search term with wild card is ‘Advert*’. Below that are two tables of results.

In the first result table in B8:G8, XLOOKUP is used to return multiple columns of data, rather than just the result from a single column. The third argument returns columns B to G, spilling right across the entire result table.

=XLOOKUP(B5,Accounts3[Account Description],Accounts3[[New Account]:[HST Status]],"",2,1)

However, what if there are multiple instances of ‘Advert*’ in the source table?

XLOOKUP can return the first instance, or the last instance by adjusting the sixth argument. But it does not (yet) support the ability to return an array with multiple hits. Hopefully that will be included in a future update to Excel.

Meanwhile, a formula using FILTER with a nested SEARCH provides equivalent results.

=FILTER(Accounts3[[New Account]:[HST Status]], ISNUMBER(SEARCH(B5,Accounts3[Account Description])))

SEARCH returns the starting position of one text string inside another, and supports wildcard searches. In this example, ISNUMBER determines whether SEARCH returns a numeric value or an error. In cases where SEARCH yields a number, that row is included in the FILTER result.

Summary

While HLOOKUP and VLOOKUP will likely be supported in Excel by Microsoft for years to come, there is probably no longer a use case for these that can not be handled better by XLOOKUP.

Download the XLOOKUP demo workbook.

Here are links to the Microsoft documentation for the XLOOKUP, IS and SEARCH functions.

by Pat Martens

Excel SEQUENCE Function

A final function to complete the dynamic array toolkit. Like some other DA functions, the function appears simple at first, but provides great versatility when combined with other DA functions such as UNIQUE, SORT and FILTER. For a discussion of dynamic arrays, see the intro to this series.

Usage

SEQUENCE provides the ability to create dynamic sequences of numbers or dates. This function is helpful with setting up amortization tables or forecasting models. It can even be used at the core of a dynamic calendar.

Arguments

The arguments are as follows.

Examples

The examples below are available for download in the SEQUENCE demo workbook.

Example 1

To generate a column of 20 numbers starting at 10 and stepping by 10, the function would be as below.

=SEQUENCE(10,1,10,10)

To set up a row of years, starting from 2020 for a 5-year forecasting model, use the following formula.

=SEQUENCE(1,5,2020,1)

In the demo workbook, use the SEQ_Numbers worksheet and adjust the amounts in the blue shaded cells to see how the results respond.

 

Example 2

In the SEQ_Dates worksheet the function is similar, but results are formatted as dates.

With this example, one could generate a column of the next 6 Mondays, starting March 1, 2021, or column headings for an Accounts Receivable aging report.

Example 3

Until dynamic arrays were available in Excel, it was most common to provide scalar arguments to functions, that is, arguments that had a single value. However, now many Excel functions will accept arrays as arguments as well. This opens up many possibilities, especially for the SEQUENCE function.

(For those familiar with array or CSE or <Ctrl><Shift><Enter> formulas, that was and still is a way to work with array-based arguments and results. Though that capability is not dynamic in the way that the new DA functions are.)

On the LoanCost worksheet of the demo workbook, the objective is to calculate total cost of a loan, provided with loan duration, interest rate and loan amount. This can be calculated in a single cell in B6.

Start by reviewing the formula in cell A12. This uses the IPMT() function to calculate the interest portion of the loan payment for period 1.

=-IPMT(B4/12,1,B3*12,B5)

The second argument in the IMPT() function is the period for which to calculate interest. In cell B12, the 1 (scalar value) has been replaced with an array {2;3}. An array is formatted between curly brackets and separated by a semi-colon. This causes the result to spill, and return the interest portion of the loan payments for periods 2 and 3.

=-IPMT(B4/12,{2;3},B3*12,B5)

The formula in cell C12 then takes this to the next level by replacing the array {2;3} with a SEQUENCE() function containing all the values for the months in the loan, or B3 (loan duration in years) X 12.

=-IPMT(B4/12,SEQUENCE(B3*12),B3*12,B5)

Finally, to calculate total interest cost of the loan defined in cells B3 to B5, enclose the formula in cell C12 in a SUM().

=SUM(-IPMT(B4/12,SEQUENCE(B3*12),B3*12,B5))

Example 4

In the LoanAm worksheet, the SEQUENCE() function plays a key role in the construction of a dynamic loan amortization table.

Cells B3 to B5 contain the same inputs as the previous example. Cell B6 now uses the Excel PMT() function to calculate the monthly loan payment.

Cell D10 starts the amortization table with the initial loan amount.

In row 11, the table calculations begin.

The Month column uses SEQUENCE() to return the month numbers, based on loan duration in years multiplied by 12.

The Interest column uses IPMT() function together with SEQUENCE to calculate monthly interest, as demonstrated in the previous example.

The Principal column subtracts the interest from the payment. To use the correct amount of interest for the month, the formula references B11#, or the array of values calculated in the Interest column.

Finally, the running Balance column uses a nested SUMIFS() function to reduce the loan balance by the total principal paid to date.

Next…

Bonus: The SEQUENCE demo workbook contains a Calendar worksheet which demonstrates a dynamic month calendar based on the use of SEQUENCE(), WEEKDAY() and IF().

While this wraps up the series of posts about functions released to specifically leverage the dynamic array capabilities in Excel, two additional posts will cover the versatile XLOOKUP() function and the new LET() function.

Download the SEQUENCE demo workbook.

Here is a link to the Microsoft documentation for the SEQUENCE function.

by Pat Martens

Excel UNIQUE Function

This continues a multi-part discussion of some new features introduced in Excel in 2019 and 2020. This describes the capabilities of the UNIQUE function, especially when used in combination with previously reviewed dynamic array functions SORT and FILTER. For a discussion of dynamic arrays, see the intro to this series.

Usage

This is the function for which many have been waiting for some time. UNIQUE returns a list of unique values from a longer list. This functionality can also be provided by using a pivot table, or sorting and grouping a list. But in both of those situations, the resulting list does not update automatically as the dynamic array returned by UNIQUE does.

Arguments

The arguments are as follows.

Examples

These examples are available for download in the demo workbook. Sample data is fictional but is set in the context of a post-secondary institution.

Example 1

Starting with a raw list of financial transactions in a table called AR_Transactions that would be either linked to or exported from an accounting system, the objective is to compute current account balances.

Two formulas can be used to set this up. In F2, use the UNIQUE function to return a list of the names of students with transactions in the list. For readability, use the name of the table and column, rather than referring to the explicit cell range that includes the table and column.

=UNIQUE(AR_Transactions[Student])

In G2, use SUMIFS to simply add all the of transactions for each student.

=SUMIFS(AR_Transactions[Amount],AR_Transactions[Student],F2#)

The F2# refers to the entire dynamic array returned by UNIQUE into F2 and spilling down.

In the demo workbook for download, the SORT_UNIQUE worksheet has exactly the same data and table, but nests UNIQUE inside the SORT function to return a sorted list of student names.

Of course, the most frequent question here is which student owes the most? A simple way to show this is to set up a dynamic array in cell I2 using this formula.

=SORT(F2#:G2#,2,-1)

This returns the spilled results in F2 and G2, sorts by the second column (the Balance), in descending order.

Example 2

In this example, UNIQUE is used in combination with SORT to check for address errors or inconsistencies in a dataset of donor records.

The donor information is in a list in columns A to C. The data in this set is of course completely fictionalized, but the names of towns are from central Canada where Plains Edge is based.

Cells E2 and F2 contain the following formulas.

E2   =SORT(UNIQUE(B2:B301))

F2   =SORT(UNIQUE(C2:C301))

By sorting the unique results, many errors in the addresses are revealed. The village of Kleefeld is spelled two different ways, La Broquerie West is sometimes abbreviated in two different ways, and the name of Landmark is spelled incorrectly at least once.

Canadian provinces, like US states, can be abbreviated with two letters. AB and MB are correct abbreviations for Alberta and Manitoba. However, NO is not a valid identifier and is probably a transposition error for ON, or Ontario.

Application

In practice, the UNIQUE function is a tool that is often useful when evaluating a large dataset. How many customers are represented in the table? How many classifications are actually in use?

Used in combination, UNIQUE, SORT and FILTER functions can be used to create dynamic arrays in a wide variety of situations.

Download the UNIQUE demo workbook.

Here are links to the Microsoft documentation for the UNIQUE function.

by Pat Martens