Lesson#156: XLOOKUP vs VLOOKUP in Excel. Which one is better?

Hello Reader! Welcome to my other post on Excelabcd. Microsoft Excel has introduced a new function on Excel 365 and Excel online version. Which is the Excel XLOOKUP function. Which is the DAD of all LOOKUP functions. Here we shall discuss XLOOKUP vs VLOOKUP differences. We shall also discuss advanced features of XLOOKUP function and some tips about XLOOKUP.

Don’t need to select the whole table and count columns no

Ok, let’s share an example. Here in this table, I shall look up how much HARRY got in HISTORY.

1st I shall use the VLOOKUP function.

Here I have to select lookup_value, all the table_array then I have to provide column no ‘3’ to get the result.

But where in XLOOKUP you don’t have to worry about column no.

Just simply select the lookup_value, lookup_array, and return_array, and the work is done. You don’t need to worry about counting column no. when it’s a big data table.

Don’t need to put Lookup_value on the Left-most column

Whereas in the case of VLOOKUP, the lookup_value should be on the left-most column. But in XLOOKUP, it doesn’t matter.

If I need to know what is the name of roll no. 34 then it is not possible with VLOOKUP. Otherwise, I have to change the position of the column or I need to use INDEX-MATCH combination. But in XLOOKUP the problem is solved.

XLOOKUP includes returning if the error value feature

If we need to return a default value for an error that occurred in VLOOKUP then we have to combine the IFERROR function with VLOOKUP. But in the case of XLOOKUP, it includes a feature to return the default value when an error happens.

Horizontal search

XLOOKUP can search the table horizontally, which is impossible for VLOOKUP. We need HLOOLUP to perform a horizontal lookup. HLOOKUP has the same type of limitations as VLOOKUP.

Key differences XLOOKUP vs VLOOKUP in a table

Features included	VLOOKUP	XLOOKUP
Exact match default	No	Yes
Return value to the right of the lookup	Yes	Yes
Return value to the left of the lookup	No	Yes
Lookup in row	No	Yes
Return more than one value	No	Yes
The lookup column needs to be sorted	Approximate match only	Binary search only
Search from top to bottom	Yes	Yes
Search from bottom to top	No	Yes
Binary search	No	Yes
Custom message if lookup value not found	No	Yes
Wildcard search	Yes	Yes
Exact search	Yes	Yes
Approximate search returning next smaller value	Yes	Yes
Approximate search returning next larger value	No	Yes
May return an incorrect value if lookup values are not sorted?	Yes – approximate	No – approximate, Yes – Binary

Subscribe to the channel for more excel related videos and tutorials.