When working with large datasets, manually searching for specific information is not just tedious—it’s prone to error. This is where Excel lookup functions come in. They act like a powerful search engine for your spreadsheets, allowing you to instantly pull corresponding data from different tables, sheets, or even entirely different workbooks.
Whether you are trying to find an employee’s salary based on their ID, or retrieving product prices from a master catalog, mastering lookup formulas is a must-have skill for any data professional. Below, we dive into the top 5 lookup functions in Excel, complete with practical examples to help you elevate your spreadsheet game.
1. XLOOKUP: The Ultimate Modern Replacement
Introduced in newer versions of Excel, XLOOKUP is the successor to both VLOOKUP and HLOOKUP. It is incredibly robust: it searches in any direction (left or right), handles exact matches by default, and can even return custom text if a match isn’t found without needing an IFERROR wrapper.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Practical Example 1: Basic Left-to-Right Lookup
Find the price of a product based on its item code.
| A (Item Code) | B (Product) | C (Price) | D (Search Code) | E (Formula Result) | |
|---|---|---|---|---|---|
| 1 | 101 | Keyboard | $25 | 102 | =XLOOKUP(D1, A1:A3, C1:C3) |
| 2 | 102 | Mouse | $15 | $15 | |
| 3 | 103 | Monitor | $150 |
Practical Example 2: Searching Right-to-Left
Unlike VLOOKUP, XLOOKUP can pull data to the left of your search column. Let’s find an Item Code based on the Product name.
| A (Item Code) | B (Product) | C (Search Item) | D (Formula Result) | |
|---|---|---|---|---|
| 1 | 101 | Keyboard | Monitor | =XLOOKUP(C1, B1:B3, A1:A3) |
| 2 | 102 | Mouse | 103 | |
| 3 | 103 | Monitor |
Pro Tip for Handling Errors
Use the 4th argument of XLOOKUP to handle missing data easily: =XLOOKUP(D1, A:A, B:B, "Not Found").
2. VLOOKUP: The Classic Standard
VLOOKUP (Vertical Lookup) is arguably the most famous Excel function. It searches for a value in the first (left-most) column of a table array and returns a value in the same row from a column you specify.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Practical Example 1: Finding Employee Departments
Retrieve the department of an employee by searching their ID number.
| A (ID) | B (Name) | C (Department) | D (Search ID) | E (Formula Result) | |
|---|---|---|---|---|---|
| 1 | E01 | Sarah | Sales | E02 | =VLOOKUP(D1, A1:C3, 3, FALSE) |
| 2 | E02 | Mike | IT | IT | |
| 3 | E03 | Anna | HR |
Practical Example 2: Approximate Matches for Grading
By setting the last argument to TRUE, VLOOKUP can find approximate matches—perfect for assigning letter grades based on scores.
| A (Min Score) | B (Grade) | C (Student Score) | D (Formula Result) | |
|---|---|---|---|---|
| 1 | 0 | F | 85 | =VLOOKUP(C1, A1:B4, 2, TRUE) |
| 2 | 60 | D | B | |
| 3 | 75 | C | ||
| 4 | 80 | B |
3. INDEX & MATCH: The Powerful Duo
Before XLOOKUP existed, combining the INDEX and MATCH functions was the preferred method for advanced users to overcome the limitations of VLOOKUP. It remains highly popular, incredibly fast on large datasets, and allows for dynamic two-way lookups.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Practical Example 1: Overcoming Left-to-Right Limitation
Just like XLOOKUP, INDEX & MATCH can look to the left. Let’s find an employee ID based on their name.
| A (ID) | B (Name) | C (Search Name) | D (Formula Result) | |
|---|---|---|---|---|
| 1 | E01 | Sarah | Anna | =INDEX(A1:A3, MATCH(C1, B1:B3, 0)) |
| 2 | E02 | Mike | E03 | |
| 3 | E03 | Anna |
Practical Example 2: Two-Way Matrix Lookup
You can use two MATCH functions inside an INDEX function to look up data at the intersection of a specific row and column (e.g., finding the sales of a specific product in a specific month).
| A (Product) | B (Jan) | C (Feb) | D (Search) | E (Formula Result) | |
|---|---|---|---|---|---|
| 1 | Keyboard | 150 | 200 | Keyboard + Feb | =INDEX(B1:C2, MATCH("Keyboard", A1:A2, 0), MATCH("Feb", B1:C1, 0)) |
| 2 | Mouse | 100 | 130 | 200 |
4. HLOOKUP: The Horizontal Searcher
HLOOKUP works exactly like VLOOKUP, but horizontally. Instead of searching for a value in the first column and moving to the right, it searches for a value in the first row and moves down to return a value.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Practical Example 1: Finding Monthly Targets
If your spreadsheet has months listed across the top row instead of down a column, HLOOKUP is what you need to extract the data.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Metric | Jan | Feb | Mar |
| 2 | Sales | $5000 | $6000 | $5500 |
| 3 | Target | $4500 | $5500 | $6000 |
To find the Target for Feb: =HLOOKUP("Feb", A1:D3, 3, FALSE) returns $5500.
Practical Example 2: Dynamic Tax Bracket Retrieval
Use an approximate HLOOKUP (set to TRUE) to find tax rates based on horizontal income brackets.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Income | 0 | 50000 | 100000 |
| 2 | Rate | 10% | 20% | 30% |
For an income of $65,000: =HLOOKUP(65000, B1:D2, 2, TRUE) returns 20%.
5. LOOKUP: The Classic Vector Search
The LOOKUP function is one of the oldest in Excel. While largely superseded by XLOOKUP and VLOOKUP, it remains incredibly powerful for specific array formulas and backward compatibility. It assumes your search data is sorted in ascending order.
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Practical Example 1: Finding the Last Value in a Column
A clever trick using LOOKUP is finding the last numeric value in a dynamic column. Because LOOKUP ignores errors, we can use a division trick.
| A (Values) | B (Formula Result) | |
|---|---|---|
| 1 | 15 | =LOOKUP(2, 1/(A1:A5<>""), A1:A5) |
| 2 | 42 | |
| 3 | 22 | |
| 4 | 42 | |
| 5 |
Note: This advanced formula creates an array of 1s and errors, and looks for the number 2, forcing Excel to return the very last valid item!
Practical Example 2: Basic Vector Lookup
Search a sorted single column (vector) to return a value from the same position in another single column.
| A (Sorted Age) | B (Category) | C (Formula Result) | |
|---|---|---|---|
| 1 | 0 | Child | =LOOKUP(25, A1:A3, B1:B3) |
| 2 | 18 | Adult | Adult |
| 3 | 65 | Senior |
Conclusion
Retrieving data efficiently is at the core of any spreadsheet workflow. While VLOOKUP has been the gold standard for decades, diving into INDEX & MATCH and upgrading to the phenomenal XLOOKUP will drastically speed up your data analysis and reduce errors.
Practice these top 5 lookup functions in Excel using the examples above, and you’ll quickly realize why they are considered indispensable tools for professionals across all industries. Ready to learn more advanced formulas? Stay tuned to Learn Excel and Sheets for more guides!
