## What is **XLOOKUP** function in Excel?

The **XLOOKUP** function is one of the Lookup & reference functions of Excel.

It searches a range or an array for a match and returns the corresponding item from a second range or array.

By default , an exact match mode is used.

We can find this function in **Lookup & reference** category of insert function Tab.

## How to use XLOOKUP function in excel

- Click on an
**empty cell**(like F5).

2. Click on the **fx icon **(or press shift+F3).

3. In the **insert function tab** you will see all functions.

4. Select **Lookup & reference** category.

5. Select **XLOOKUP** function

6. Then select **ok**.

7. In function arguments Tab, you will see **XLOOKUP **function.

8. **Lookup** **value** is the value to search for.

9. **Table_array** is the array or range to search.

10. **Return_array** is the array or range to return .

11. **If_not_found** is returned if no match is found.

12. **Match_mode** specifies how to match lookup_value against the values in lookup_array.

13. You will see the **result **in the formula result section.

`=XLOOKUP`**(A2,A5:A14,E5:E14,"not found",0)**----->>>>answer is 175

## Examples of XLOOKUP function in excel

**Example 1:**

### How to find a specific value in a table Excel?

We want to find a specific value in a table . for example the height, age, and weight of a person named “Elijah” from the following data:

`=XLOOKUP`**(H5,A5:A14,B5:B14)**----->>>>answer is 22
=XLOOKUP**(****H5**,A5:A14,C5:C14)----->>>>answer is 198
=XLOOKUP**(****H5**,A5:A14,D5:D14)----->>>>answer is 185
=XLOOKUP**(****H5**,A5:A14,E5:E14)----->>>>answer is 175

**Example 2:**

### Is Xlookup vertical or horizontal in excel?

The xlookup function searches the data vertically and horizontally in the table.

```
=XLOOKUP(A2,A5:A14,B5:B14)----->>>>answer is 22
```

```
=XLOOKUP("Age",A5:E5,A9:E9)----->>>>answer is 22
```

**Example 3:**

### How to match exact data in XLOOKUP?

The xlookup function in its default mode finds the data as exact match. In this case, the match mode is in position 0.

```
=XLOOKUP(A2,A5:A14,B5:B14,,0)----->>>>answer is 22
```

**Example 4:**

### How to match exact data (next smaller item) in XLOOKUP?

In this case, the match mode is in position -1. If nothing is found, it will show the next smaller item.

```
=XLOOKUP(A2,A6:A14,B6:B14,,-1)----->>>>answer is 9%
```

**Example 5:**

### How to match exact data (next larger item) in XLOOKUP?

In this case, the match mode is in position -1. If nothing is found, it will show the next larger item.

```
=XLOOKUP(A2,A6:A14,B6:B14,,+1)----->>>>answer is 12%
```

**Example 6:**

### How to match exact data (by wild card match) in XLOOKUP?

You can find the answer by searching a part of the desired data.In the example below, we search for the last three letters of the word along with *.

```
=XLOOKUP("*jah",A5:A14,B5:B14,,2)----->>>>answer is 22
```

In the example below, we search for the first three letters of the word along with *.

```
=XLOOKUP("Eli*",A5:A14,B5:B14,,2)----->>>>answer is 22
```

In the example below, we search for the middle three letters of the word along with *.

```
=XLOOKUP("*ija*",A5:A14,B5:B14,,2)----->>>>answer is 22
```

**Example 7:**

### How to return more than one value in XLOOKUP?

XLOOKUP can return more than one value at the same time for the same match.

```
=XLOOKUP(A2,A5:A14,B5:D14,,)----->>>>answer is {22,198,185}
```

**Example 8:**

### How to do two way lookup or matrix lookup using xlookup?

we can use two-way XLOOKUP for searching any specific value in table.

`=XLOOKUP`**(B2,B5:D5,XLOOKUP(B1,A6:A14,B6:D14))**----->>>>answer is 22
=XLOOKUP**(B1,A6:A14,XLOOKUP(B2,B5:D5,B6:D14))**----->>>>answer is 22

By this formula and by changing the name or any characteristic, the desired output can be achieved. for example

`=XLOOKUP`**(B2,B5:D5,XLOOKUP(B1,A6:A14,B6:D14))**----->>>>answer is 235

## What is the purpose of XLOOKUP function?

It searches a range or an array for a match and returns the corresponding item from a second range or array.

## What is the Return value of **XLOOKUP** function?

It can return any type of data.

`XLOOKUP(Lookup value,Table_array,Return_array,If_not_found)=number,text`

## How many arguments does **XLOOKUP **function have?

`XLOOKUP`**(Lookup value,Table_array,Return_array,If_not_found,Match_mode)**

This function has just **5** Arguments.

**Lookup** **value** is the value to search for.

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

**Return_array** is the array or range to return.

**If_not_found** is returned if no match is found.

**Match_mode** specifies how to match lookup_value against the values in lookup_array.

**Match_mode** and

**If_not_found**arguments of

**XLOOKUP**function is optional.

## Which version of Excel supports **XLOOKUP** function?

This function is available for just excel **version **2021.

## Errors in XLOOKUP function

If the VLOOKUP function does not find any items, it returns a not found (**#N/A** ) error.

`VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is `**#N/A**

If the input of the function is not typed correctly, it returns a not found (**#N/A** ) error.

`VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is `**#N/A**

If extra space is placed at the input, it returns a not found (**#N/A** ) error.

`VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is `**#N/A**

If the table range is not entered correctly, it returns a not found (**#N/A** ) error.

`VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is `**#N/A**

If the **Col_index_num** is not entered correctly, it returns a not found (**#VALUE**) error.

`=VLOOKUP(H4,A2:E10,-1,FALSE)----->>>>answer is `**#VALUE**