In the last week of August, Microsoft announced a new function ‘XLOOKUP’ as an upcoming successor of famous functions such as VLOOKUP and HLOOKUP. Before I start to Explain what is XLOOKUP and How to use XLOOKUP Function, I want to tell you about VLOOKUP‘s incredible history.
VLOOKUP was in existence since the First version of excel in 1985, 34 years ago. Since then it has been the 3rd most used function of Microsoft Excel. Every Excel user started his way to become a professional by using the function VLOOKUP. But VLOOKUP has some notable limitations, to overcome them Microsoft has decided to introduce a VLOOKUP killer function i.e. XLOOKUP.
You will be able to use XLOOKUP in newer versions of Microsoft office to come, for now, it is just available for the Office Insiders. So you have some more period to wait for before you can start using this function.
How XLOOKUP is Better than VLOOKUP?
By reading this, you will come to know how XLOOKUP is superior to VLOOKUP and HLOOKUP, and how It is going to make up for the limitations of VLOOKUP.
What is XLOOKUP?
XLOOKUP was announced on Wednesday, August 28, 2019, for a certain percentage of Office Insiders. It is named so because it can look in Multiple directions like Horizontal and verticle. It has 5 arguments that will fulfill all your lookup needs. The structure of the XLOOKUP is as shown.
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match Mode], [Search Mode])
We will how to use XLOOKUP in detail further in this article.
Why release a new Xlookup function?
While excel users were using VLOOKUP function widely, It has some limitations which are fixed by the XLOOKUP function, though the advanced users can turn to INDEX/MATCH function but most of the users find it difficult. The Key Features/Advantages of XLOOKUP over VLOOKUP are as follows.
- Can perform lookup to the left: VLOOKUP searches for the first column and returns the value which is to the right. Users have to rearrange their data to lookup value which is to the left. while XLOOKUP can Find a match anywhere irrespective of the direction.
- Can find the last match: In XLOOKUP, we can find the last match as well, which is not possible in VLOOKUP which gives only the first match.
- Defaults to an exact match: XLOOKUP defaults to an exact match which is its 4th argument. whereas VLOOKUP defaults to an Approximate match.
- Has Speed Improvements: XLOOKUP has all the speed improvements of VLOOKUP 2018. The performance of XLOOKUP is better as we specify only two columns instead of the whole table.
- Can insert/delete columns: Using XLOOKUP user can insert or delete the columns as it does not rely on the column number like VLOOKUP.
- Can search for the next large item: XLOOKUP has the 4th argument which is ‘Exact‘ by default but you can also search for the next large value.
- Returns Range instead of Value: XLOOKUP returns the range, unlike the VLOOKUP which returns a value. So it can be useful in Data Validation.
How to Use XLOOKUP function?
XLOOKUP is easy to Use. We will take a detailed look at how to use the XLOOKUP function. The basic XLOOKUP Function can even be used with only 3 arguments but if you want to perform an advanced Lookup you can use all five arguments of the function. Its syntax is as follows.
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
Let us go by an example. Suppose in the given data of the companies we have to find the ‘Company Name‘ by using the ‘Customer ID‘. Let’s try with VLOOKUP first.
Oops! VLOOKUP cannot be used in this Scenario as the Column index number is -2 which is less than 0, so you will get the #VALUE! error. VLOOKUP cannot find the value to the left of the lookup value it only works in the right direction.
Let’s try by Using the XLOOKUP function this time. Follow the steps to understand how to use the XLOOKUP function.
STEP 1: Specify the Lookup_Value i.e. the value you are looking for in the data table. In this case, it is cell G4 (Choose Customer ID).
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
STEP 2: Specify the Lookup_array i.e. the column in which you are looking for your Lookup_Value. In this case, it is Column C. Don’t forget to freeze it by pressing F4.
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
STEP 3: Specify the Return_Array i.e. which column you want to return. In this case, it is Column A. press F4. Notice how we can select a single Column In XLOOKUP which contains the return value, whereas in VLOOKUP we have to select the whole Table. The return_array is on the left-hand side of the lookup_array, which is again not possible in VLOOKUP.
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
STEP 4: Match_Mode allows you to select the type of match you want. The 4 options are:
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
- Use 0 to perform an exact match. XLOOKUP defaults as an exact match
- Use -1 to look for an exact match or the next smaller match in case no exact match is found.
- Use 1 to look for an exact match or the next larger match in case no exact match is found.
- Use 2 for wildcard character match
In our case, we need an exact match so we will keep it 0. Notice how 4th and 5th arguments are in square brackets it means they are optional.
STEP 5: Search_Mode allows you to select the type of Search you want XLOOKUP to perform. The 4 options are:
=XLOOKUP(Lookup_Value, Lookup_Array, Return_Array, [Match_Mode], [Search_Mode])
- Use 1 to perform the search from Top. XLOOKUP defaults as a First to Last search mode
- Use -1 to perform the search from Bottom/Last
- Use 2 and -2 to perform Binary searches on Sorted data which is ascending or descending.
In Our case, we need to search from first to last so we will keep it 1.
Press ENTER. Now the XLOOKUP is giving up the value ‘Fastners co.’ from column A. Our XLOOKUP is working perfectly where VLOOKUP could not do good.
What Happens to VLOOKUP and HLOOKUP?
VLOOKUP and HLOOKUP both will continue to be used in excel for now. Once you get your hands on XLOOKUP I suggest you to use the function because it is similar, simple and more effective.
Even though XLOOKUP is currently available for Microsoft Office 365 Insider program, that too for limited members. It might be coming out soon for everyone in future updates.
Please comment below if you liked this article or if you have any queries regarding this new function. Thank you!
To send your ideas and Suggestions to Excel Community use UserVoice. You can follow Excel on Facebook and Twitter to stay updated with Microsoft Excel.