For years, VLOOKUP was the go-to for finding information in a table. But it has a major weakness: it can only look for data in the first column and retrieve information from columns to its right. What if you need to look up a value and grab data from a column to its left? That's where INDEX and MATCH come in. They are two separate functions that, when combined, create a lookup formula that is far more powerful and flexible than VLOOKUP.

Let's see how they work together. Imagine you have a list of products. You know the Product ID and want to find the product name.

First, we use MATCH to find the row number of the Product ID "SP-003". The formula would be MATCH("SP-003", C2:C5, 0). This tells Excel to look for "SP-003" in the range C2:C5. The 0 means we want an exact match. This formula returns the number 3, because "SP-003" is the third item in that list.

Next, we use INDEX to retrieve the value from the Product Name column (A2:A5) at that position. The formula becomes: =INDEX(A2:A5, MATCH("SP-003", C2:C5, 0))

Because MATCH returns 3, the formula simplifies to =INDEX(A2:A5, 3), which returns "SummitPack". This combination can look left, right, up, or down, making it incredibly versatile.

Often, your calculations need to change based on certain conditions. That’s the job of logical functions. The most common one is IF, which checks if a condition is true or false and then returns a value based on the outcome.

The basic structure is =IF(condition, value_if_true, value_if_false).

But what if you have multiple conditions? You can nest other functions inside IF. The AND function returns TRUE only if all of its arguments are true. The OR function returns TRUE if any of its arguments are true.

Let’s say we want to give a 10% bonus to employees who both exceeded $50,000 in sales and have been with the company for more than 2 years. We can nest the AND function inside an IF statement.

Here’s how it breaks down:

1. AND(B2>50000, C2>2) checks if sales in cell B2 are over $50,000 and tenure in C2 is over 2 years.
2. If both are true, the IF function calculates the bonus (10% of their salary in D2).
3. If either condition is false, it returns 0.

Array formulas let you perform multiple calculations on one or more sets of values at once. Instead of calculating a result for each row and then summing that result, an array formula can do it all in a single cell.

For example, if you have a list of item quantities in column B and their prices in column C, you could calculate the total revenue with one formula:

This formula multiplies each quantity by its corresponding price and then sums up all those results. It's like creating a temporary "total price" column in memory and adding it up, all without cluttering your spreadsheet.

In older versions of Excel, you had to press Ctrl+Shift+Enter to confirm an array formula, which would wrap it in curly braces {}. In modern Excel, you can just press Enter, and it handles the array logic automatically.

Advanced formulas can sometimes produce errors like #N/A, #VALUE!, or #DIV/0!. These aren't just ugly; they can also break other formulas that depend on them. A clean way to manage these is with the IFERROR function.

IFERROR checks if a formula evaluates to an error. If it doesn't, it returns the formula's result. If it does, it returns a value you specify.

Let’s revisit our INDEX-MATCH example. If someone searches for a Product ID that doesn't exist, the formula returns #N/A. We can wrap it in IFERROR to show a friendlier message.

Now, instead of a jarring error code, the cell will simply display "Not Found". This makes your spreadsheets more robust and user-friendly.

Time to test what you've learned.

By combining these advanced functions, you can build powerful, dynamic models that handle complex data with ease.