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.
0 Comments on “Lesson#156: XLOOKUP vs VLOOKUP in Excel. Which one is better?”