XLOOKUP vs VLOOKUP – 5 Key Differences
The Lookup functions are one of the most very popular formulas in Excel. They are used to search for one value in a column and return the corresponding value from another column. Over the years, the VLOOKUP function has become one of the most frequently used and must know lookup formulas. But, Microsoft Excel had recently introduced a new lookup function called XLOOKUP for its latest Office 2019 and Office 365 versions. In this article, we’ll compare XLOOKUP vs VLOOKUP and see which one is the better option for searching values in Excel.
The XLOOKUP Function
The XLOOKUP function is an improved version of various Excel LOOKUP functions. Use it to search a specific range or an array of a specified value in a worksheet, both vertically and horizontally. And, it returns the corresponding value in another column.
Knowing exactly how it works can be helpful. Here is the syntax with arguments:
Syntax
| XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
Parameters
- Lookup_value – Specifies the value to search for.
- Lookup_array – It denotes the cell range where the function has to search.
- Return_array – Specifies the range or array to extract the values.
- [if_not_found] – This is optional, and it is the value or message to return if there’s no match. If it is not mentioned, the formula returns a #N/A error.
- [match_code] – This is optional, and you can specify 0, -1, 1, or 2 depending on the match type you want to perform.
0 – It returns an exact match is found or else it returns #N/A.
-1 – It returns an exact match if found. Else returns the next smaller data.
1 – It returns an exact match when found. Else returns the next larger data.
2 – It returns a wildcard match where *,? and ~ have a special meaning.
- [search_mode] – This too is optional, and you specify 1, -1, 2, or -2 depending on the direction of the search.
1 – It starts its searches from the first data. This is the default option.
-1 – It searches from the last data, in the reverse order.
2 – It performs a binary search and requires that the lookup_array be sorted in ascending order. The function will return an invalid result if the lookup_array is not sorted.
-2 -It performs a binary search and requires that the lookup_array be sorted in descending order. The function will return an invalid result if the lookup_array is not sorted.
XLOOKUP Example
In the example below, the query is to return the First_Name of the employee who works as an HR. The lookup_value is in cell B9 as HR. The formula to find that is entered in cell C9 as =XLOOKUP(B9, D2:D6, B2:B6,,0,1).
You can notice that XLOOKUP is performing a backward search. The lookup_value is in column D and the result has to be searched in column B. This kind of backward search is not possible in any other lookup function.
In the optional fields, the first field, [if_not_found] is left empty. The second optional field, [match_code] is mentioned as 0 to return the exact value. The third field, [search_mode], is mentioned as 1 to begin the search from the first field. That is the reason the formula returns the answer as Edith.
The query is to find the First_Name of the employee who works in the HR department. You can find that there are 2 employees, Edith and Robert, in the HR department.
To return the other employee’s name, Robert, use the same formula and just mention the third parameter as -1. This will begin its search in the reverse order from the bottom. The name Robert is seen first and hence it returns the same.
The VLOOKUP Function
In simple words, the VLOOKUP function works like an index page in a book where we look at the topic to locate its page number.
The VLOOKUP function searches for the value across columns. Basically, it searches vertically and hence the name VLOOKUP where ‘V’ stands for vertical.
The function takes a lookup value, table reference, and column index as mandatory parameters. The last parameter is optional, which specifies the return value. It could either return an exact match or an appropriate match.
It is mandatory that the VLOOKUP function has a known value as an argument. Now, let’s look at the syntax and understand it with an example.
Syntax
| VLOOKUP ( lookup_value,table_array,col_index_num,[range_lookup]) |
Parameters
- Lookup_value – This is the value to search across the cells, get the match, and return the corresponding value from the other column.
- Table_array – This argument takes the starting value and the ending value of the cells the function has to search.
- Col_index_num – The value in this argument represents the index of the column to look into.
- range _lookup – It accepts 2 values, one is TRUE and another is FALSE. If TRUE is mentioned in the formula, then the function returns the closest match. If FALSE is mentioned, the function returns the exact match.
VLOOKUP Example
In the example above, we have an employee table that contains the details including Emp_ID, First_Name, Last_Name, Dept, and Salary.
The data is spread across the cells from A2 to E6. This cell range is referred to as table_range. The query here is to find the employee’s salary if the Emp_ID is given.
Imagine the same situation in a real-world environment where a manager seeks an answer from a table that has thousands of rows. This is where VLOOKUP is used to automate the search and obtain the exact result.
The formula used to find the salary of an employee whose Emp_ID is 27 is VLOOKUP(C8, A2:E6, 5, FALSE).
Cell C8 contains the lookup_value 27. The formula searches for this value in the table spread across the range A2 to E6. The column number is 5 because it is in the 5th column from where the lookup_value is referenced.
The last parameter is an optional parameter and we mention it as FALSE so it returns an exact match. If it is mentioned as TRUE, the function returns an appropriate match if an exact match is not found.
The formula works like this:
VLOOKUP(C8,..,..,..) – takes the value at C8, which is 27.
VLOOKUP(C8, A2:E6,..,..) – searches for 27 in the cells from A2 to E6. It finds it in column A, 5th row.
VLOOKUP(C8, A2:E6, 5,..) – From column A and the 5th row, it moves 5 columns left as mentioned in the formula. Here, 5 is the column index.
VLOOKUP(B9, B2:F6, 5, FALSE) – The exact value is available in the 5th column which is the salary column. It returns the value of 25,000 which is the salary of the employees whose ID is 27.
XLOOKUP vs VLOOKUP – Key Differences
| XLOOKUP | VLOOKUP | |
| 1 | XLOOKUP searches for data both horizontally and vertically. | VLOOKUP searches only vertically. |
| 2 | XLOOKUP can refer to the left of the lookup_value. | It always looks up the values towards the right of the lookup_value. |
| 3 | It has optional fields, the match_mode, and the search_mode. | VLOOKUP does not have optional fields. |
| 4 | In case a match cannot be found in the lookup_table, XLOOKUP supports an optional parameter called not_found which overrides the #N/A error. | VLOOKUP returns the default error message #N/A error. |
| 5 | In XLOOKUP, you need to specify the lookup_value column and the return_value column alone. | In VLOOKUP, you need to specify the entire table_array. |
Closing Thoughts
In this article, we learned how the XLOOKUP and the VLOOKUP function work with the help of a few examples. The XLOOKUP function, unlike other lookup functions, can work vertically and horizontally with the same syntax. You only need to ensure that you provide values for the lookup and the return array to get the result you desire.
Use VLOOKUP to search across a huge database when you are sure that it is a fixed table and the items to be searched are on the right.
Please note that the XLOOKUP function is only available in Excel for Office 365. Older Excel versions starting with Excel 2019 don’t have this function. There are other LOOKUP formulas to use that are explained in other articles which you can access from our blogs.
We recommend you use XLOOKUP instead of VLOOKUP in all future spreadsheets. If you have any questions, please feel free to ask us and share your experience in the comments section.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel training courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
