You've likely used VLOOKUP to find data in a table. It's a classic, but it has limitations. You can only search in the leftmost column, and it's prone to errors if you add or remove columns. Meet its modern replacement: XLOOKUP.

The basic syntax is simpler than its predecessors:

Let's say we have a table of employee data and we want to find the department for a specific Employee ID. Here's our data:

To find the department for Employee ID 103, we would use this formula. Notice how we just select the ID column and the Department column. The order doesn't matter.

Excel searches for 103 in the lookup_array (A2:A5), finds it, and returns the corresponding value from the return_array (C2:C5), which is "Engineering".

Sometimes you need to sum numbers that meet several criteria at once. For this, SUMIFS is the perfect tool. It extends the capability of SUMIF by allowing you to specify multiple conditions.

Imagine we have this sales data and want to calculate the total sales for the "East" region for just "Laptops".

Here's the SUMIFS formula:

This formula tells Excel to sum the values in C2:C6 only if the corresponding value in A2:A6 is "East" and the value in B2:B6 is "Laptop". The result would be $2,700 (1,200 + 1,500).

Newer versions of Excel introduced a game-changing feature: dynamic arrays. In the past, if a formula was meant to return multiple results, you had to use complex key combinations. Now, Excel handles it automatically. When a formula can produce multiple results, it simply "spills" them into the adjacent empty cells.

Two of the most useful dynamic array functions are FILTER and SORT. You enter the formula in one cell, and the results fill out as needed.

The FILTER function lets you extract a range of data based on criteria you define. It's like an advanced filter, but the results are dynamic and update automatically if your source data changes.

Using our sales data from before, let's pull all records from the "East" region.

If you type this into cell E2, Excel will spill the results, creating a new table automatically:

Now, let's take it a step further. We can wrap our FILTER function inside a SORT function to organize the results. The SORT function can sort a range or array of data.

Let's sort our filtered list of "East" region sales in descending order based on the sales amount (the 3rd column).

Here, 3 tells SORT to use the third column of the filtered array for sorting, and -1 specifies descending order. The result is a clean, sorted, and filtered list that updates whenever your original data does.

Ready to test your knowledge?

Mastering these functions will significantly speed up your ability to analyze and manipulate data.