WHICH IS BETTER – XLOOKUP OR VLOOKUP ?

Table of Contents

INTRODUCTION

EXCEL is the best spreadsheet tool as of now with great functionalities and it is getting better with every release.

LOOKUP FUNCTIONS are the most powerful and helpful functions which help us lookup for a value and get the data from various reports and create a final report.

These functions help us to fetch the data from various big reports and create a more valuable and useful report.

In this article we’ll discuss the difference between two great functions VLOOKUP and XLOOKUP and find out the difference between them and conclude which one is better.


WHAT IS VLOOKUP FUNCTION ?

Although we would like to keep it specific about the topic but the basic information about the functions is a must.

VLOOKUP enables us to LOOKUP for a particular value or text from another TABLE and return ANY content from the cells corresponding to that looked up value.

In simple words,

Vlookup simply lets us to search for a value in a table [ SIMPLE ROWS AND COLUMNS] , and help us to retrieve any value in the row of the searched value.

Click here to learn more about VLOOKUP.

WHAT IS XLOOKUP FUNCTION?

XLOOKUP is a new generation LOOKUP FUNCTION in Excel.

This function is found only in Microsoft 365 and later.

XLOOKUP IS AN OFFICE 365 FUNCTION WHICH HELPS US TO LOOK UP FOR A VALUE AND RETRIEVE ANY OTHER VALUE OR A COMPLETE ARRAY OF VALUES AGAINST THE LOOKED UP VALUE.

XLOOKUP simply provides more functionality , more flexibility and more power.

CLICK HERE TO LEARN MORE ABOUT XLOOKUP.



BENEFITS OF VLOOKUP FUNCTION IN EXCEL

For our comparison,

let us check out the benefits of VLOOKUP FUNCTION which has been around for many many years and has been one of the favorite of many Excel users.

So let us try to enlist the great things about this function which made it so popular.

Few benefits of using VLOOKUP FUNCTION are as

  • Simple to use.
    • The vlookup function is very easy to use. Simply understand the syntax and use it. Only one parameter is having codes which you need to take care about.
  • Powerful
    • VLOOKUP FUNCTION lets you join or combine very big reports consisting of thousands of rows within seconds.
  • Less number of parameters
    • Vlookup has only four parameters. The lookup value, lookup table, returning column and exact or approx match. The number is comparatively smaller for the job it does for us.
  • A single package
    • The function is a single package and doesn’t need to be written like INDEX-MATCH where we use one function inside the other. Simpler the function, easier it’s troubleshooting is.
  • Easy to understand
    • Vlookup is not very hard to understand once you learn it properly.
  • Less parameters to remember
    • As we already discussed it has only four parameters and parameter with options is just one which has only two options. APPROXIMATE or EXACT match.

So, these were a few good points about the VLOOKUP FUNCTION.

Now let us have a look at its limitations.


LIMITATIONS OF VLOOKUP FUNCTION

No function is complete.

As we try to include more and more flexibility , it is actually complicating the functions.

Few limitations of VLOOKUP are

  • Compares only the first column.
    • The LOOKUP VALUE in the VLOOKUP FUNCTION is compared to the first column of the LOOKUP TABLE which is a limitation. You can’t lookup value in any other column but first column only.
  • Returns only single value.
    • You’ll retrieve only one value at a time. One use of function will return one value only.
  • Only look to the Right Direction.
    • VLOOKUP compares the lookup value with the first column and can return only the values to the right of the lookup table. It can’t retrieve the values to the left.
  • Not case sensitive
    • Vlookup has no sense of CASE of the text. GOOD and good has the same meaning and it’ll return whatever it finds first.
  • Approximate Matching by default.
    • The fourth argument asks whether you want the approximate result or exact result. If you omit the argument, the default is the approximate result although we use the exact matching most of the time practically.
  • Returns the first match.
    • If we are searching for the word HELLO and the lookup table has 3 HELLOs, the function will return the values against the first HELLO only.

BENEFITS OF XLOOKUP FUNCTION IN EXCEL

XLOOKUP function also offers a number of great features.

Let us discuss a few of them.

Some of the great features are :

  • Search the Left or the Right as per your wish
    • The function gets the lookup array and return array separately which allows the function to lookup in any direction. The lookup value can be to the Right of the returning value.
  • Return a complete array in one go.
    • Xlookup need not to be applied again and again to get multiple values in a row but it has the power to get the complete row as an array.
  • Create a custom error message.
    • The custom error message can be set as per requirement.
  • Choose the search direction.
    • We can set the search direction whether we want the search from top to bottom or bottom to top.
  • Custom Matching.
    • You can match a value or next higher, or the value or next lower.
  • Exact Matching by default.
    • If we don’t mention, the comparison will be exact by default.
  • Can carry out Vertical as well as Horizontal Lookup.
    • Xlookup can work like VLOOKUP as well as HLOOKUP i.e. can work horizontally as well as vertically.
  • Can use BINARY SEARCH.
    • Xlookup can make use of binary search.
    • Binary search is a search algorithm which is used to search an item from a sorted list. It is available in Xlookup.

LIMITATIONS OF XLOOKUP FUNCTION IN EXCEL

Even though XLOOKUP offers great features, there are a few limitations too.

  • Can’t search Case sensitive match
    • No case sensitive matching. Xlookup too can’t recognize between HELP and help.
  • Return the first match only.
    • It also returns only the first match only. Although we can control the direction from top to bottom or bottom to top.
  • Large number of options.
    • The full syntax of xlookup is quite big and arguments have many options which might need a bit of the understanding.
  • Hard to remember the complete configuration.
    • As the syntax is big, it is somewhat hard to remember. Although Excel provides you help while typing, even then unless until you understand the formula thoroughly , you are highly susceptible to errors.
  • Available only for OFFICE 365. [ Although a simple addin is present here ]
    • As of now XLOOKUP is only available to OFFICE 365 for which you need the subscription. It is not available for other Excel versions like 2019, 2016, 2013, 2010 and lower.

XLOOKUP VS VLOOKUP

Here comes the most important section.

Although XLOOKUP has come years after VLOOKUP , it is expected that XLOOKUP would have taken away many of the limitations of the VLOOKUP.

XLOOKUP function is actually an improvement of the VLOOKUP FUNCTOIN.

So after knowing the benefits and limitations of both the functions individually, it is time for a HEAD ON COMPARISON so that we can conclude if we should go with the XLOOKUP or not.

Let us go for a simple HIGH SCHOOL TYPE of comparison.

FEATUREXLOOKUPVLOOKUP
SEARCH LEFT OR RIGHTYESNO
RETURN A SINGLE VALUEYESYES
RETURN A COMPLETE ARRAY IN ONE GOYESNO
CAN WORK HORIZONTALLY OR VERTICALLYYESNO
DEFAULT EXACT MATCHYESNO
CASE SENSITIVE SEARCHNONO
EASY TO REMEMBER NOYES
CAN SEARCH TOP TO BOTTOM OR BOTTOM TO TOPYESNO
RETURNS FIRST MATCHYESYES
BINARY SEARCHYESNO
CUSTOM ERROR MESSAGEYES NO
SIMPLENOYES



CONCLUSION

In this article, we learned about VLOOKUP and XLOOKUP, checked their benefits and issues in details.

We had a HEAD ON COMPARISON at the end.

Vlookup has always been applauded as a life saver whenever we need to join or combine many reports.

Xlookup is definitely based upon the VLOOKUP but with greater strength, lesser limitations.

The greatest issues were the ability to look only to the Right which was removed with the introduction of XLOOKUP FUNCTION.

Another big issue , although it wasn’t an issue but Xlookup has brought this great strength of returning a complete array at once.

Just imagine that if we have TEN COLUMNS, we need to use the VLOOKUP TEN TIMES. Different parameters and ten functions need to be checked.

But in case of XLOOKUP, we need just one function in place of Ten functions.

So, with this discussion and the major improvements, XLOOKUP is the clear winner.

But the availability is the issue.

XLOOKUP is only available for the OFFICE365 EXCEL APPLICATION and not for the previous versions.

But we have created a small addin to perform the XLOOKUP FUNCTION. [ CLICK HERE TO GET XLOOKUP ADDIN FOR FREE ]

If we don’t have XLOOKUP, we can still go with the VLOOKUP with some adjustments like keeping the search column at the PLACE 1.

If we don’t want it, we can also make use of INDEX MATCH pair, which will remove the direction limitation of vlookup.


WHEN SHOULD I USE VLOOKUP OR XLOOKUP ?

Xlookup is the clear winner in the battle.

But wait!!

It doesn’t mean that you need to use Xlookup every time.

Xlookup has a big list of options. If you are not using xlookup frequently, you’ll surely forget it and you’ll need to refer the syntax over and again , which is a time consuming process.

If we forget the syntax, again we have to take a reference and again refresh it by learning, which is again time consuming.

On the other hand, Vlookup is having comparatively lesser options which can be learnt easily.

So, use vlookup where you have to return only one value or simpler operations and xlookup when you have big arrays to be returned.

In this article we checked out the vlookup and xlookup functions, their pros and cons to find out the better one.