Intro

VLOOKUP has been one of the most widely used function in Excel. But there's another newer and more modern function, XLOOKUP. It's more capable in almost every way. In this article we'll be looking at the differences and similarities between the two functions.

VLOOKUP

VLOOKUP is a function in Excel to look up data in a table organized vertically. Lookup values must appear in the first column of the table in order for it to work. It's probably the better known function between the two. It's easy to use. It scans a table, finds a match, and returns a result. But it has limitations unlike XLOOKUP or even INDEX and MATCH. It needs a complete lookup value in the first column. Making it more difficult to use VLOOKUP with multiple criteria. And because of its default matching it's easy to get incorrect results.

Below is how a VLOOKUP works:

VLOOKUP searches the first column of a table and returns a corresponding value in the same row in another specified cell.

The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

• lookup_value is the value to look for
• table_array is the range of vertical data (must contain the lookup values)
• column_index_num is the column number of the value to get
• range_lookup is optional but it controls match behavior, if it's TRUE it will perform an approximate match, if it's FALSE it will perform an exact match

A major thing to note. VLOOKUP can only look to the right. It can only retrieve data to the right of the column that has the lookup values. If you need to lookup values to the left see XLOOKUP below.

Example

Below is an example of a VLOOKUP. In this case we are trying to find the email that belongs to ID 218. In the VLOOKUP we have:

• G6 - the value of what we are trying to lookup
• B6:E14 - the entire table we are looking at
• 4 - the column index number to look at the "email" column

It returns kjones@company.com

XLOOKUP

This is a modern and flexible replacement for VLOOKUP and other types of lookups. It supports more like approximate and exact matching and lookups in vertical or horizontal ranges. Unlike VLOOKUP it can search data starting from the first value or last value.

Below is how XLOOKUP works.

The syntax:

=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

• lookup is the lookup value
• lookup_array is the array or range to search
• return_array is the array or range to return
• not_found this is optional, which is value to return if no match
• match_mode is optional, 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
• search_mode is optional, 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending

Example

Below is an example of XLOOKUP . In this case we are trying to find (an exact match) the first name that belongs to ID 218. In the XLOOKUP we have:

• G6 - the value of what we are trying to lookup
• D6:D14 - the lookup array
• B6:B14 - the return away

It returns Anna.

Differences

Below are the main differences between VLOOKUP and XLOOKUPs. There are more suitable differences between the two but these are the most significant.

VLOOKUP

Can lookup data to the right of lookup values

Performs an approximate match by default

Can only search vertically

XLOOKUP

Can lookup data to the right or left of lookup values

Performs an exact match by default

Can search vertically and horizontally

Use Cases

Now when would you want to use one over the other? Generally speaking everything that the VLOOKUP can do XLOOKUP can do. But below is when you might want to use one over the other:

VLOOKUP

• Intuitive - it scans through the first column in the table. When it finds a match, it moves across the table to the column number given and gets the value in the same row.
• Popular - It's widely used, there are spreadsheets everywhere that use this function. If you're sharing this with someone else it may be better to use it because it's more commonly seen.
• Simple - If you have a table with the lookup values in the first column, it's easy to use the VLOOKUP

XLOOKUP

• Reverse search - it can search in both directions (forward or reverse). No need to use another function to look the right of a table
• Two-way search - It can search vertically and horizontally, there's no need to use another function if the data isn't in a vertical orientation
• Robust - because it uses a normal cell reference for the return_array it's less fragile than VLOOKUP. Inserting or deleting columns won't break the formula

Conclusion

Excel's introduction of XLOOKUP as a modern alternative to the traditional VLOOKUP improves a lot of VLOOKUPs shortcomings. While VLOOKUP's simplicity may be appealing for basic vertical searches, XLOOKUP's versatility in handling both vertical and horizontal searches, ability to look in both directions, and resilience to column alterations make it a more robust and flexible option. For Excel users looking for an adaptable lookup function, XLOOKUP is the better choice.