No history yet

Advanced Formulas

Smarter Data Lookups

You've probably needed to pull a piece of information from one table into another. Maybe you have a list of product IDs and you need to find their prices from a separate master list. This is where lookup functions shine. They find a value in one place and return a related value from another.

The classic tool for this is VLOOKUP. The 'V' stands for vertical. It scans down the first column of a table to find what you're looking for, then moves right to grab information from a specified column in that same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

// lookup_value: What you want to find.
// table_array: The range where you're looking.
// col_index_num: The column number to return data from.
// range_lookup: FALSE for an exact match, TRUE for an approximate match.

While VLOOKUP is popular, it has a major weakness: it can't look to its left. The value you're searching for must be in the first column of the range you specify. If you need to find a product ID and return a brand name listed in a column to the left, VLOOKUP won't work.

For years, the solution was a powerful combination: INDEX and MATCH.

Think of INDEX as a map of your data. You give it a range and specify a row and column number, and it tells you what's there. MATCH finds the position of a value in a list. When you combine them, MATCH finds the row number you need, and INDEX retrieves the data from that row in any column you want, even one to the left.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

// return_range: The column with the data you want back.
// lookup_value: What you want to find.
// lookup_range: The column to search for the lookup_value.
// 0: Specifies an exact match.

This duo is flexible and robust. Unlike VLOOKUP, it doesn't break if you insert or delete columns within your table. But it's also a bit clunky to write.

Enter XLOOKUP, the modern successor to both. It's simpler to write and more powerful by default.

Lesson image

XLOOKUP directly asks for what you're looking for, where to look, and what range to return data from. No more counting columns. It defaults to an exact match, which is usually what you want anyway.

=XLOOKUP(lookup_value, lookup_array, return_array)

// lookup_value: What you're searching for.
// lookup_array: The range to search in.
// return_array: The range to get the corresponding value from.

It also has optional arguments for what to do if the value isn't found, the match mode, and the search direction. If you have access to it, XLOOKUP is the best choice for almost all lookup tasks.

Working with Dynamic Arrays

Traditionally, an Excel formula lives in a single cell and returns a single result. Dynamic Arrays change that. A single formula can now return multiple results that automatically fill, or "spill," into neighboring empty cells.

This fundamentally changes how you work with data. Instead of writing a formula and dragging it down a column, you write one formula that populates the entire range. Let's look at three of the most useful dynamic array functions: FILTER, SORT, and UNIQUE.

The FILTER function lets you extract a subset of data that meets specific criteria. Imagine you have a large table of sales data and you only want to see the records for a particular region. FILTER does this in one step.

=FILTER(array, include, [if_empty])

// array: The range of data you want to filter.
// include: A condition (e.g., A2:A100="East") that returns TRUE or FALSE.
// [if_empty]: Optional text to show if no results are found.

Next is SORT. As the name suggests, it sorts a range of data. You can sort by any column, in ascending or descending order.

=SORT(array, [sort_index], [sort_order])

// array: The range to sort.
// [sort_index]: The column number to sort by.
// [sort_order]: 1 for ascending (default), -1 for descending.

You can even wrap a FILTER function inside a SORT function. For example, =SORT(FILTER(...)) would first filter your data and then sort the results. This is called nesting, and it's a powerful way to chain operations together.

Finally, UNIQUE is perfect for pulling a distinct list of items from a column that contains duplicates. If you have a long list of customer names and want to see each customer only once, UNIQUE is the tool.

=UNIQUE(array)

// array: The range from which you want to extract unique values.

Readable and Reusable Formulas

As your formulas get more complex, they can become difficult to read and debug. Two newer functions, LET and LAMBDA, help solve this problem by making your logic cleaner and more reusable.

The LET function allows you to assign names to calculation results within a formula. Think of it as creating temporary variables. This is incredibly useful when you need to use the same calculated value multiple times in a single formula. Instead of writing the calculation over and over, you calculate it once, give it a name, and then use that name.

=LET(name1, name_value1, calculation_using_name1)

// Example: Calculate sales tax
=LET(subtotal, SUM(A1:A10), subtotal * 0.08)

This makes the formula easier to read and also more efficient, as Excel only has to compute subtotal once.

The LAMBDA function takes this a step further, allowing you to create your own custom, reusable functions without needing to write complex scripts. A LAMBDA is a generic formula that you define with placeholders for your inputs.

=LAMBDA(parameter1, parameter2, calculation)

// Example: A generic formula to add two numbers and multiply by a third
=LAMBDA(x, y, z, (x+y)*z)

To use a LAMBDA, you can either call it immediately by adding parentheses with the inputs at the end, like =LAMBDA(x, (x*1.1))(B2), or you can assign it a name using the Name Manager. For example, you could create a function named ADD_TAX that takes a value and adds 20%.

By giving your LAMBDA a name, you can use it anywhere in your workbook just like a built-in Excel function: =ADD_TAX(100) would return 120. This is the key to creating a library of custom calculations tailored to your specific needs.

Quiz Questions 1/6

What is the primary limitation of the VLOOKUP function?

Quiz Questions 2/6

A formula using a dynamic array function, like FILTER, can return multiple results that automatically fill into neighboring cells. This behavior is called 'spilling'.

Mastering these advanced functions will dramatically expand what you can accomplish in Excel, turning complex data challenges into manageable tasks.