XLOOKUP Part 2 – Advanced functions

In this article you'll find more advanced analyses you can perform using XLOOKUP. If you're looking for the basic XLOOKUP functions, we invite you to check out XLOOKUP Part 1 – The evolution of VLOOKUP and INDEX MATCH.

The use cases you'll find in this article:

• Searching within matrices (by row and column)
• Summing over a variable range
• Finding the closest value

1. Searching within matrices (by row and column)

You can search across entire matrices, using both horizontal and vertical search terms.

XLOOKUP("what you want to look up horizontally", "where you want to look horizontally", XLOOKUP("what you want to look up vertically", "where you want to look vertically", "matrix where the result is located"))

2. Summing over a variable range

Suppose you have a database with historical sales by month, using the months as headers along the horizontal axis. Now, if you wanted to build a formula that sums sales from January to June, or April to September, or October to December, and lets you change the range automatically, XLOOKUP is the solution.

SUM(XLOOKUP("what you want to look up", "where you want to look", "where the result is located") : XLOOKUP("what you want to look up", "where you want to look", "where the result is located"))

3. Finding the closest value

A rather useful feature when you want to look up a number within certain defined ranges. For example, if the person who has to approve an invoice varies depending on the amount and you need to validate against an extensive matrix of approvers:

XLOOKUP("what you want to look up", "where you want to look", "where the result is located", [if not found], [match mode])