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

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.

XLOOKUP vs VLOOKUP

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.

XLOOKUP vs VLOOKUP

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 vs VLOOKUP
XLOOKUP vs VLOOKUP

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.

See also  Lesson#110: Separate characters from the text where any word appears
XLOOKUP vs VLOOKUP
XLOOKUP vs VLOOKUP
XLOOKUP vs VLOOKUP

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 includedVLOOKUPXLOOKUP
Exact match defaultNoYes
Return value to the right of the lookupYesYes
Return value to the left of the lookupNoYes
Lookup in rowNoYes
Return more than one valueNoYes
The lookup column needs to be sortedApproximate match onlyBinary search only
Search from top to bottomYesYes
Search from bottom to topNoYes
Binary searchNoYes
Custom message if lookup value not foundNoYes
Wildcard searchYesYes
Exact searchYesYes
Approximate search returning next smaller valueYesYes
Approximate search returning next larger valueNoYes
May return an incorrect value if lookup values are not sorted?Yes – approximateNo – approximate,
Yes – Binary

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

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

0 Comments on “Lesson#156: XLOOKUP vs VLOOKUP in Excel. Which one is better?

Leave a Reply

Your email address will not be published. Required fields are marked *

*