Spreadsheets often hold vast amounts of data. Finding one specific piece of information can feel like searching for a needle in a haystack. Excel provides powerful lookup functions to do this work for you, saving you from endless scrolling.

The most common lookup tool is VLOOKUP, which stands for vertical lookup. It scans down the first column of a table to find a specific value, then moves across that row to pull data from another column. Imagine you have a price list and need to find the price for a specific product ID.

To find the price of "Tea Leaves" (ID 102), you can use VLOOKUP. The function needs four pieces of information:

1. What to look for: The product ID, 102.
2. Where to look: The entire table of data.
3. Which column to return: The price is in the 3rd column.
4. Match type: You want an exact match, so you specify FALSE.

This formula tells Excel to find 102 in the first column of the range A2:C4, then return the value from the 3rd column of that same row. The result would be $12.50.

There's also HLOOKUP for horizontal lookups, which works the same way but scans across the first row instead of down the first column. It’s less common because most data is organized vertically.

VLOOKUP is handy, but it has a major limitation: it can only look for values in the very first column of your selected table. If you need to find a product ID based on its name, VLOOKUP won't work. For this, we can use a more powerful combination: INDEX and MATCH.

These two functions work as a team.

• MATCH finds the position of a value in a list. For example, it can tell you that "Tea Leaves" is the 2nd item in the product name column.
• INDEX retrieves a value from a specific position in a list. You can tell it to get the 2nd item from the product ID column.

By nesting them, you create a dynamic and flexible lookup.

Let's break that down from the inside out:

1. MATCH("Tea Leaves", B2:B4, 0) looks for "Tea Leaves" in the range of product names (B2:B4) and finds it in the 2nd position. The 0 specifies an exact match.
2. The MATCH function returns the number 2.
3. INDEX(A2:A4, 2) then takes that 2 and returns the 2nd value from the product ID range (A2:A4), which is 102.

This combination is more robust because your lookup column and your return column can be anywhere. They don't have to be in a specific order.

Beyond finding data, Excel can also make decisions based on conditions you set. This is done with logical functions, and the most fundamental one is IF.

The IF function checks if a condition is true or false and then returns a value you specify for each outcome. It follows this structure: IF(logical_test, value_if_true, value_if_false). For instance, you could check if a student's score is a passing grade.

If the score in cell A2 is 60 or higher, the formula returns "Pass." Otherwise, it returns "Fail."

But what if you need to test multiple conditions? That's where AND and OR come in. The AND function returns TRUE only if all of its conditions are met. The OR function returns TRUE if at least one of its conditions is met.

You can use these inside an IF statement. To give a bonus to a salesperson who sold over 100 units and had sales over $5,000, you'd use AND.

For situations with more than two possible outcomes, you can nest IF statements inside each other. For example, let's assign letter grades based on a score.

Here, Excel first checks if the score is 90 or above. If it is, it returns "A" and stops. If not, it moves to the second IF statement, which checks if the score is 80 or above. If true, it returns "B." If both tests fail, it returns "C."

These lookup and logical functions are foundational tools for transforming static data into dynamic insights. By mastering them, you can build smarter, more responsive spreadsheets.