How to use XLOOKUP Function in Excel

There is a new function which is called as XLOOKUP. XLOOKUP is available in latest version of Microsoft Excel. 

If your version of Excel have XLOOKUP function you can use XLOOKUP. XLOOKUP is advanced version of VLOOKUP and it is easy to use.

In the below table if you need to get the City in a Country as per the Country Capital Name you can use VLOOKUP or XLOOKUP but if you need to get Country Name as per Country Capital you cannot use VLOOKUP but you can use XLOOKUP.

Because in VLOOKUP result after the Lookup Value can be taken and in XLOOKUP you can go for before or after the Lookup Value. Lets see this using an Example.

In the below Table you need to get Country name based on Capital Name. We can apply XLOOKUP.

In the above table you can see Country, Capital and City Name. If you want to check Capital Name or City Name as per Country Name, you can use VLOOKUP Function but if you need to find Country Name as per Capital name then you cannot use VLOOKUP. There you can use XLOOKUP Function.

Lets see how

Use the below formula to get the result. 

XLOOKUP(B14,C2:C10,B2:B10,0)

Here B14 - Lookup Value - In the example capital name

C2:C10 - Lookup array - in the example its Capital Name

B2:B10 - Return Array - in the example its Country Name

Using XLOOKUP function we need to copy the Lookup Array and Return Array for getting the result. 

For eg. In VLOOKUP we need to copy the entire table and give the column number to get the result but in XLOOKUP just provide the Lookup array and return array. No need to copy the entire table and count the column number for getting the result.