XLOOKUP and VLOOKUP are two of the most popular Lookup and Reference functions in Excel. When our clients schedule advanced Microsoft Excel training, these functions always generate a lot of interest.

While both functions are incredibly useful, they each come with different capabilities and limitations you should be aware of.

## Whare Are Lookup Functions in Excel?

Excel has an entire collection of functions in the Lookup & Reference category, including ADDRESS, LOOKUP, HLOOKUP, VLOOKUP, and XLOOKUP.

So what do lookup functions do exactly?

**Lookup functions let you find a value in a data list or table then return a related value from the same row (or column).**

An example might be helpful.

## How to Find a Lookup Value

Think about the departures board at an airport. Here is a photo from the airport in Los Angeles (LAX).

If someone asked you what time American 6183 is scheduled to leave, how would you find out?

For a lot of people, this is a pretty familiar scenario.

You would scan down the left column until you find American 6183… then you would locate the departure time from another column in the same row. American Airlines flight 6183 is scheduled to leave at 3:55PM.

This is exactly what the XLOOKUP and VLOOKUP functions do!

## How to Use the VLOOKUP Function in Excel

The VLOOKUP function in Excel is used to locate a return value in a range or table with a vertical orientation. In other words, the data is listed in rows.

The VLOOKUP function has the following syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

**lookup_value** is what you’re looking up.

**table_array** is range where the lookup value is located.

**col_index_num** specifies the column that contains the return value.

**range_lookup** is an optional parameter. True = approximate match, False = exact match. If omitted, range_lookup defaults to True.

### Using VLOOKUP to Find an Exact Match

Let’s take a look at Excel’s VLOOKUP function in action. In the following example, we’ll find out which gates various flights will depart from.

In cell B4, I typed **=VLOOKUP(A4,$D$1:$H$21,3,FALSE)** and pressed **Enter**.

Here’s the rundown on all of the parameters used in the VLOOKUP function:

- Cell
**A4**contains the value I want to look up (China 5). **$D$1:$H$21**is the range containing my lookup table.- The return value is in column
**3**. - I want an exact match so I set range_lookup to
**False**.

### Important Details About VLOOKUP

VLOOKUP always parses the first column in *table_array* for your lookup value.

*Table_array* would need to be sorted by column 1, if we were looking for an approximate match (*range_lookup* = True).

It’s best to use absolute cell references when specifying *table_array*, if you want to copy the VLOOKUP function to additional cells.

## How to Use the XLOOKUP Function in Excel

Microsoft introduced XLOOKUP in Office 365 and Excel 2021. It provides more flexibility then VLOOKUP but it isn’t available in older versions of Excel.

The XLOOKUP function is used to locate a return value in a range or table with a vertical or horizontal orientation. In other words, XLOOKUP can be used to find data in rows or columns!

The XLOOKUP function has the following syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

**lookup_value** is what you’re looking up.

**lookup_array** is the range or array to search.

**return_array** is the range or array containing values you want to return.

**if_not_found** is an optional parameter that specifies text to display if no match is found.

**match_mode** is an optional parameter that specifies match type. 0 = exact match or #N/A if not found (default), -1 = exact match or next smaller item, 1 = exact match or next larger item, 2 = wildcard match with *, ?, or ~.

**search_mode** is an optional parameter that specifies how to search for results. 1 = start with first item (default), -1 = start with last item, 2 = binary search where lookup_array is sorted in *ascending* order, -2 = binary search where lookup_array is sorted in *descending* order.

### Using XLOOKUP to Find an Exact Match

Let’s illustrate how Excel’s XLOOKUP function works by looking at a couple of examples. We’ll start by finding out which gates various flights will depart from.

In cell B4, I typed **=XLOOKUP(A4,$D$1:$D$21,$F$1:$F$21)** and pressed **Enter**.

Let’s go over each of the parameters in the XLOOKUP function:

- Cell
**A4**contains the value I want to look up (China 5). **$D$1:$D$21**is the lookup array.**$F$1:$F$21**is the return array.

### Important Details About XLOOKUP

XLOOKUP parses the range you specify in *lookup_array* for your lookup value. The lookup array can be a row or column.

After finding the specified lookup value, XLOOKUP returns related data from *return_array*. *return_array* needs to be the same size as *lookup_array*, otherwise a #VALUE error will be displayed.

Absolute cell references should be used when specifying *lookup_array* and *return_array*, if you intend to copy the XLOOKUP function to adjacent cells.

XLOOKUP defaults to an exact match.

## Useful XLOOKUP Features

The XLOOKUP provides more flexibility than VLOOKUP. Let’s look at a couple of features that make XLOOKUP so powerful.

In the following example, I used XLOOKUP to find out which flight is assigned to various gates at the airport.

In cell B4, I typed **=XLOOKUP(A4,$G$2:$G$21,$E$2:$E$21,”Unassigned”)** and pressed **Enter**.

Let’s go over each of the parameters in the XLOOKUP function:

- Cell
**A4**contains the value I want to look up (101). is the lookup array,**G$2:$G$21****$E$2:$E$21**is the return array. The return array is positioned to the left of the lookup array. This wouldn’t be possible using VLOOKUP.- I added the text “Unassigned” as the
**if_not_found**argument so, rather than displaying an error, XLOOKUP up displays Unassigned.

## Key Differences: XLOOKUP and VLOOKUP

When you’re trying to decide which function is best in regards to XLOOKUP vs VLOOKUP, there are a few things you should keep in mind.

XLOOKUP is only available in the latest versions of Excel. Microsoft introduced it in Office 365 and Excel 2021 so not everyone is going to have XLOOKUP. VLOOKUP, on the other hand, is available in every version of Excel.

XLOOKUP is more powerful and flexible than VLOOKUP. XLOOKUP works data in rows or columns and you have more control over how lookup data is parsed.

The lookup array doesn’t have to be in the leftmost column of your lookup table and you can add or remove columns without having to redefine your return array in XLOOKUP.

In the end, it comes down to compatibility. If your organization has standardized on Office 365 or Excel 2021, you should use XLOOKUP wherever you can. If your colleagues and business partners are still using older versions of Excel, you better stick to VLOOKUP!