For years, VLOOKUP was the go-to for finding information in a table. But it has limitations, like only being able to search in the leftmost column. Its successor, XLOOKUP, is more powerful and flexible.

XLOOKUP lets you search for a value in any column and return a corresponding value from any other column, even one to the left. Its syntax is also more intuitive.

Imagine a simple table with employee data in columns A, B, and C: Employee ID, Name, and Department. If you have an Employee ID in cell E2 and want to find their department, you could use this formula:

Before XLOOKUP, the most flexible way to look up data was by combining two functions: INDEX and MATCH. This combination is still incredibly useful, especially in older versions of Excel or when you need more complex lookups.

Here’s how they work together:

1. MATCH finds the position of a value in a row or column. It returns a number (e.g., the 5th item in the list).
2. INDEX retrieves a value from a specific position within a range.

By nesting MATCH inside INDEX, you can tell Excel to first find the row number for your item, then retrieve the value from a specific cell in that row.

Using the same employee data, here's how you'd find a department for the ID in E2:

Sometimes you need to work with a range of cells that might change in size or position. The OFFSET function is perfect for this. It returns a reference to a range that is a specified number of rows and columns from a starting cell or range.

Think of it like giving directions: "Start at this cell, go down 3 rows, go right 1 column, and then select a range that is 5 rows tall and 2 columns wide."

A common use is to calculate the sum of the last 7 entries in a list that keeps growing. If your list of daily sales is in column B, starting at B2, this formula would sum the last 7 days:

Modern Excel introduced a major change in how formulas work: dynamic arrays. With dynamic arrays, a single formula can return multiple results that automatically fill, or "spill," into adjacent empty cells. You no longer need to drag formulas down or use complex array syntax.

Functions like UNIQUE, FILTER, SORT, and SEQUENCE are all dynamic array functions. For example, to get a list of all unique departments from our employee list in C2:C100, you just need one simple formula in a single cell.

Excel will spill the list of unique departments into the cells below the formula. If there isn't enough empty space for the results to spill, you'll see a #SPILL! error. Just clear the obstructing cells, and the results will appear.

Logical functions allow you to introduce decision-making into your spreadsheets. The most fundamental is the IF function, which checks if a condition is true and returns one value if it is, and another if it's false.

For example, to check if a sale in cell F2 is greater than $500:

You can make conditions more complex by combining IF with AND and OR.

• AND requires all conditions to be true.
• OR requires at least one condition to be true.

To give a bonus only if an employee is in the "Sales" department (C2) and their sales (G2) exceeded $10,000, you would nest an AND function inside IF:

This is an example of a nested formula, where one function is used as an argument inside another. You can nest multiple IF statements to handle more than two outcomes, creating layered logic to tackle complex scenarios.

Let's review these functions.

Now, test your knowledge.

Mastering these functions will dramatically improve your ability to analyze data and automate calculations in Excel.