What you will learn
Whether you’re working in finance, operations, or marketing, lookup functions are an essential part of any Excel user’s toolkit. They allow you to bring data from one table into another based on a matching value — saving hours of manual work.
In this guide, we’ll walk you through everything you need to know about lookup formulas in Excel. From legacy functions like VLOOKUP and HLOOKUP to the more powerful and flexible XLOOKUP, we’ll cover it all — plus a few advanced tricks.
What Are Lookup Functions?
At a basic level, lookup functions help you retrieve a value from one table (the source) into another table (the target) based on a common identifier, also known as a matching key.
For example, if you have a list of employee IDs and their corresponding names in one table, you can use a lookup to find an employee’s name based on their ID from another table.
VLOOKUP and HLOOKUP – The Legacy Formulas
VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) were the go-to functions for many years.
- VLOOKUP searches for a value in the first column of a table and returns a value from a specified column in the same row.
- HLOOKUP does the same but works across rows instead of columns.
Enter XLOOKUP – The Modern, Better Way
Microsoft introduced XLOOKUP to solve many of the limitations of its older counterparts.
XLOOKUP lets you:
- Search left or right
- Use exact match by default
- Easily add error handling
- Replace both VLOOKUP and HLOOKUP with one formula
Lookup with Multiple Matching Keys
Sometimes, a single lookup key isn’t enough. You might need to match two or more columns, like Customer + Product.
This is where the combo technique comes in — by creating a helper column that combines multiple values
Best Practice: Use Error Handling
To keep your files clean and user-friendly, always include error handling in your lookup formulas.
This ensures your spreadsheet doesn’t show ugly #N/A or #VALUE! errors.
Summary
Here’s a quick recap of what we’ve covered:
- Lookup functions help retrieve values from a table using matching keys.
- VLOOKUP and HLOOKUP are older, widely used formulas — still found in legacy files.
- XLOOKUP is the modern replacement — easier to use, more flexible, and safer.
- Use combo techniques for lookups with multiple keys.
- Always add error handling to keep your Excel models clean and professional