How to use Xlookup in Microsoft Office Excel

Basic Xlookup Function alternative to Vlookup

Today I am going to show you how to use Xlookup function in Microsoft excel. 

It is a more powerful and versatile successor of vlookup and H lookup. Its better in many ways.  

 Microsoft managed to produce a robust function that overcomes many frustrating errors and notable limitations of VLOOKUP. 

 

It can look vertically and horizontally, to the left and above, search with multiple criteria, and even return multiple values at same time.  

But there is one catch. Currently XLOOKUP is only available to Microsoft 365 subscribers. 

Let’s get started with Basic XLOOKUP formula. 

To gain more understanding, let's build an Xlookup formula in its simplest form to perform an exact lookup. For this, we will only need the first 3 arguments. 

Supposing, you have a summary table with employee information (Name, Country and ID number). You want to get the country of employee specified in field F10 and F11 (lookup value).  

 

With the employee names in C2:C5  (lookup_array) and counties in D2:D5 (return_array), the formula goes as follows: 

=XLOOKUP(F10,C2:C5,D2:D5) 

 

Search for this value, in this range, return a match from this range. 

 

 

 

 

Top 10 benefit of XLOOKUP 

• XLOOKUP works vertically and horizontally 

• XLOOKUP works to the left 

• XLOOKUP worls with exact and approximate match 

• XLOOKUP works with wildcards 

• XLOOKUP works last match 

• XLOOKUP works and return multiple values (row or column) 

• XLOOKUP works with multiple conditions 

• Two-way XLOOKUP