Image by Editor
To retrieve data from a medium to large dataset, lookup formulas are the most powerful and essential functions in Excel. For years, VLOOKUP has been the most used and reliable approach for data searching, yet it has some limitations. Excel’s newer XLOOKUP function expands on these capabilities, providing greater flexibility, easier maintenance, and more intuitive logic. In this tutorial, we will show how to master VLOOKUP and XLOOKUP in Excel, including basics to advanced techniques.
Basics: Understanding VLOOKUP and XLOOKUP
Both VLOOKUP and XLOOKUP follow the same logical steps. The ultimate goal of this function is to search for specific values from any dataset.
VLOOKUP
The VLOOKUP (Vertical Lookup) function searches for a value in the first column of a table and returns a value in the same row from a specified column. It is the most popular and widely used lookup function in Excel.
VLOOKUP Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: It is the value you want to look for
- table_array: The range of cells containing the data
- col_index_num: The column index number in the table from which to retrieve the value (first column = 1)
- range_lookup: FALSE for exact match, TRUE for approximate match (optional, defaults to TRUE)
XLOOKUP
XLOOKUP is also a lookup function, introduced in Excel 365. XLOOKUP is an advanced and dynamic function that addresses many VLOOKUP limitations. It’s simpler, more powerful, and more flexible.
XLOOKUP Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: It is the value you are searching for
- lookup_array: The column or row where Excel searches for the lookup_value
- return_array: The column or row from which Excel returns a value
- if_not_found: Value if not found (optional)
- match_mode: 0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard (optional)
- search_mode: 1=first to last, -1=last to first, 2=binary ascending, -2=binary descending (optional)
Looking for an Exact Match
Imagine an employee dataset. You want to search for the salary for a specific employee based on the Employee ID.
VLOOKUP:
=VLOOKUP(F3,A2:D61,4,FALSE)
This formula searches for the Employee ID in column A and returns the Salary from the fourth column of the table. The function is effective as long as the lookup column is the leftmost column in the range.
Important VLOOKUP Rules:
- The lookup column must be the leftmost column in your table range
- Column numbers start at 1 for the leftmost column
- Use FALSE for exact matches in most business scenarios
- Data should be sorted if using TRUE for an approximate match
XLOOKUP:
=XLOOKUP(F3, A2:A61, D2:D61)
This formula directly tells Excel to search column A for the ID and return the corresponding salary from column D. It is more straightforward because you do not need to count column numbers. In this basic scenario, both formulas are effective, but XLOOKUP is easier to read and reduces mistakes.
Handling Missing Matches
Imagine you type an Employee ID that does not exist. You want to display “Not Found” instead of an error.
VLOOKUP:
If the Employee is not found, the VLOOKUP function returns #N/A. To show a user-friendly message, you will need to wrap the function in IFERROR.
=IFERROR(VLOOKUP(I2, $A$2:$G$11, 6, FALSE), "Employee not found")
XLOOKUP:
The XLOOKUP function offers the [if_not_found] argument directly in the function. You won’t need to use any additional functions.
=XLOOKUP(I2, $A$2:$A$11, $F$2:$F$11, "Employee not found")
Both achieve the same goal, but XLOOKUP’s version is cleaner and easier to read. The difference is that XLOOKUP includes the error-message handling natively, whereas VLOOKUP requires nesting within IFERROR. Thus, XLOOKUP is cleaner and easier to handle missing values.
Intermediate Techniques: Improving Your Lookups
At the intermediate level, you can take advantage of approximate matches, layout issues solutions, using wildcards, and performing two-way lookups.
Left Lookups and Column Inserts
VLOOKUP has two well-known issues: one is left lookups, and the other is column insertion.
Left Lookup: It can only search in the first column of the table_array. If your lookup column is not the first one, you must rearrange your table or change the table_array range.
Suppose you want to search for an Employee ID based on Name.
=VLOOKUP(F3, A2:D61, 4, FALSE)
This formula returns a #N/A error. But if you rearrange the columns, then it will work.
Column Insertion: VLOOKUP uses a fixed column index number. If you insert or delete columns inside the table, your col_index_num may no longer match the correct column.
=VLOOKUP(G3, A2:E61, 4, FALSE)
This formula assumes Salary is the 4th column. If you insert a new column between B and C, Salary becomes the 5th column, and the formula silently becomes wrong.
XLOOKUP Solves These Problems:
Left Lookups:
XLOOKUP can perform “left lookups” easily and effortlessly. Just use the usual basic lookup formula.
=XLOOKUP(F3, B2:B61, A2:A61)
This formula searches Employee Name and returns the Employee ID. Doing this with VLOOKUP requires rearranging the table or using another function altogether.
Column Insertion:
XLOOKUP does not care about column position. You specify the lookup_array and return_array separately; this will return the exact value.
=XLOOKUP(G3, A2:A61, E2:E61)
Even if you insert or delete columns in the middle of the table, the lookup_array and return_array ranges will stay intact, so the formula continues to work without showing any error.
Approximate Match Lookups
Approximate match is often used for grading, commission tiers, or tax brackets, where you look up which band or threshold a number belongs to.
VLOOKUP:
When you set range_lookup to TRUE, VLOOKUP finds the closest match that is less than or equal to your lookup value. This is useful for grade scales, tax brackets, or commission tiers.
Insert a revenue value in G2. To find the correct bonus rate, insert the following formula.
=VLOOKUP(D3, A2:B6, 2, TRUE)
Here, TRUE means Excel will perform an approximate match; it finds the largest threshold that is less than or equal to G2. For this to work correctly, the first column in the table (J2:J6) must be sorted in ascending order.
XLOOKUP:
You can do the same with XLOOKUP using match_mode.
=XLOOKUP(D3, A2:A6, B2:B6, "No rate", -1)
The match_mode: -1 means “exact match or next smaller value”, which behaves like VLOOKUP’s approximate match, but you control the behavior explicitly and do not rely on omitted arguments.
Wildcard Lookups (Partial Text Searches)
Sometimes you only know part of the text you want to find, such as an Employee name that contains the word “Ma”.
VLOOKUP:
Select a cell, type the partial text, for example Mouse. Then, insert the following formula.
=VLOOKUP("*" & F3 & "*", B2:D61, 3, FALSE)
The * wildcard matches any sequence of characters. “*” & I2 & “*” means “any text that contains the text in I2”. This will return the Salary for the first matching row that contains “Ma” in the Employee column. Always set range_lookup to FALSE when using wildcards with VLOOKUP.
XLOOKUP:
XLOOKUP provides a dedicated match mode for wildcards.
=XLOOKUP("*" & F3 & "*", B2:B61, D2:D61,"Not found",2)
Here, match_mode: 2 means “wildcard match”. XLOOKUP scans the Employee column and returns the Salary for the first Employee whose name matches the pattern.
Advanced Techniques: Dynamic, Multi-Criteria, and Smart Lookups
At the advanced level, you combine multiple criteria, handle “last occurrence” lookups, and build robust, dynamic formulas that can adapt as your data changes.
Returning Multiple Related Fields at Once
Suppose you look up an employee using an ID and want to return their name, department, and salary.
VLOOKUP:
=VLOOKUP(F3, A2:D61, 2, FALSE)
=VLOOKUP(F3, A2:D61, 3, FALSE)
=VLOOKUP(F3, A2:D61, 4, FALSE)
Each formula will return a separate value.
XLOOKUP:
XLOOKUP can do this in a single formula.
=XLOOKUP(F3, A2:A61, B2:D61)
Excel automatically returns all the fields from return_array. It fills the next cells with the additional return fields. In multi-field lookups like this, XLOOKUP is significantly easier.
Multi-Criteria Lookups
Sometimes a single lookup value is not enough. You may need to look up based on multiple criteria. Suppose you want to look up the Salary based on Employee ID and Name.
VLOOKUP with a Helper Column:
One classical approach is to build a helper column that combines multiple fields. Add a helper column H with the header Helper Column.
Select cell F2 and insert the following formula.
=A2 & "|" & B2
This concatenates Employee ID and Name with a separator. Copy the formula down. Now each row has a unique key.
Insert the lookup values in H3 and H4 cells.
Now look up the Department, based on the Employee ID and Name.
=VLOOKUP(H3 & "|" & H4, F2:G61, 2, FALSE)
Here, we also swapped Salary right beside the merged column. This will return the department name. This version is a bit awkward, which is one reason XLOOKUP is preferred for multi-criteria.
XLOOKUP with Combined Arrays (Without Helper Column):
XLOOKUP lets you pass an array expression as the lookup_array. You can combine columns on the fly without creating a helper column.
=XLOOKUP(H3 & "|" & H4, A2:A61 & "|" & B2:B61, C2:C61, "Not found")
The expression A2:A61 & “|” & B2:B61 creates an array of combined values, just like the helper column did, but in memory only. XLOOKUP searches that array for the combined key and returns the corresponding Revenue.
Similar Formula but Easier Version:
=XLOOKUP(1, (A2:A61=H3)*(B2:B61=H4),C2:C61)
This searches for records matching both region AND product criteria. This approach is clean, flexible, and avoids adding extra columns to your sheet.
Getting the Last Match Instead of the First
Sometimes a value appears multiple times (for example, repeated Employee sales), and you want the most recent entry, not the first one.
VLOOKUP:
VLOOKUP always returns the first match from top to bottom. To simulate a “last match,” users sometimes sort the table so that the latest record appears first, or create helper columns with row numbers and use more complex formulas. These solutions are fragile and can be hard to maintain.
XLOOKUP with search_mode = -1:
XLOOKUP has a search_mode argument that can search from the bottom up. To find the last recorded Salary of “Helen”:
=XLOOKUP(F3, B2:B61, D2:D61, "Not found",0, -1)
Here, 0 means exact match, and -1 tells XLOOKUP to search from the last row to the first row. This directly returns the last occurrence without any helper columns or sorting tricks.
Making Formulas More Dynamic and Robust
Dynamic models are less likely to break when your data structure changes. It can auto-adjust cell changes.
Making VLOOKUP More Robust with MATCH:
To avoid hard-coding the column index number in VLOOKUP, you can use MATCH to find the column position by header name.
=VLOOKUP(F3, A2:D61, MATCH(G2, $A$1:$D$1, 0), FALSE)
The desired column name is in G2 cell, MATCH function returns the column index number.
Now, if you insert a new column into the table, the MATCH portion will update automatically, and VLOOKUP will still point to the correct column.
Fully Dynamic XLOOKUP Solutions:
XLOOKUP is naturally robust because it uses explicit ranges instead of column numbers. You can combine this with structured tables and dynamic ranges to create formulas that adapt as new rows are added.
Convert the data range to a table. Press CTRL + T or use the Data tab option. Name your table and insert the following formula.
=XLOOKUP(F3, EmployeeData[Employee ID], EmployeeData[Salary], "Employee not found")
Structured references automatically expand when you add new rows to the table, so the formula always includes your latest data without changing the ranges manually.
When to Use VLOOKUP vs XLOOKUP in Real Workbooks
Which function you will use will depend on your environment. If your environment supports XLOOKUP (Microsoft 365 or Excel 2021 or later), XLOOKUP is your default choice. XLOOKUP overcame all the limitations of VLOOKUP. It is safer against layout changes, more expressive, and often easier to read. It is easy to use, and its dynamic and robust features are beneficial for report generation.
If your lookup tasks are basic and you don’t have access to the XLOOKUP function, then VLOOKUP is still a useful function. It still matters when you share files with users on older versions of Excel that do not have XLOOKUP. Maintains legacy workbooks with many existing VLOOKUP formulas that you do not want to rewrite immediately.
In any new model or report where compatibility is not a problem, building the logic with XLOOKUP is usually the best long-term decision.
Conclusion
By following the tutorial, you can master VLOOKUP and XLOOKUP in Excel from basics to advanced techniques. We have shown several techniques by using both VLOOKUP and XLOOKUP. Both functions are essential Excel skills that dramatically improve your ability to work with data. VLOOKUP is foundational and still relevant for simple and legacy spreadsheet tasks. XLOOKUP, however, is the better choice in most modern use cases due to its flexibility, readability, and power. It is helpful for data analysis and dynamic dashboards. The best approach is to be fluent in both functions. Mastering both ensures you can work effectively with any Excel document, past or future.
