# How to use the XLOOKUP function

This article demonstrates how to use the XLOOKUP function in Excel 365.

### Table of Contents

- XLOOKUP function - example
- Function Syntax
- Function Arguments
- Video
- How to return multiple values
- How to search horizontally
- How to perform a reverse search - starting with the last item
- How to perform a binary xlookup
- How to perform a wildcard xlookup
- Get Excel *.xlsx file
- Search values distributed horizontally and return corresponding values
- Filter values distributed horizontally - Excel 365

## 1. XLOOKUP function - example

The XLOOKUP function lets you search one column for a search value, and return a corresponding value in another column from the same row.

Formula in cell F2:

The image above demonstrates the XLOOKUP function in cell F2, it uses a search value specified in cell E3 and searches column B.

A match is found in cell B5 and the corresponding value from column C is returned in cell F3.

## 2. XLOOKUP Function Syntax

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

## 3. XLOOKUP Function Arguments

lookup_value |
Required. The value you want to look up. |

lookup_array |
Required. This is a cell range or an array that you want to search. |

return_array |
Required. This is a cell range or an array that you want to return a result from. |

[if_not_found] |
Optional. A given text string is returned if a valid match is not found. #N/A is returned if this argument is omitted and a valid match is not found. |

[match_mode] |
Optional. Match setting:
0 - Exact match. Return #N/A if no valid match is found. (Default) -1 - Exact match. Return the next smaller item if no valid match is found. 1 - Exact match. Return the next larger item if no valid match is found. 2 - Wildcard match * - Any number of characters. Example, *son matches both "Johnson" and "Wilson" but not "Jones". ? - Any single character. Example, ?n matches both "in" and "on" but not "off". ~ (tilde) followed by ?, *, or ~ - Example, How~? matches How?. |

[search_mode] |
Optional. Search setting:
1 - Search starting at the first item. (Default) -1 - Reverse search starting at the last item. 2 - Binary search, -2 - Binary search, |

## 4. XLOOKUP video

## 5. How to return multiple values - XLOOKUP function

The formula below demonstrates the XLOOKUP function returning more values from the same row.

Formula in cell C6:

The *return_array *(3rd argument) contains two columns in this example. The lookup value is found in cell B8 and the corresponding values are in cell range C8:D8.

Note, the XLOOKUP function does not return multiple matches. It only returns the first valid match even if there are more valid matches.

I recommend that you use the FILTER function if you need to search for multiple values and return multiple corresponding values.

## 6. How to search horizontally - XLOOKUP function

This example demonstrates how to lookup a value horizontally using the XLOOKUP function and return the corresponding value from the same column.

Search value "Milano" is found in cell E2 and the formula returns value "C" from row three in the same column.

Formula in cell C6:

## 7. How to find the last match - reverse search

The image above shows the XLOOKUP function in cell C3, it searches cell range B6:B12 starting at the last value.

There are two matches cells B8 and B11, however, the search starts at the last value and the first matching value is in cell B11. The corresponding value is in cell C11.

Formula in cell C3:

The last argument lets you choose the search mode.

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

-1 : Reverse search starting at the last item.

This article explains how to do it in earlier Excel versions: Find last matching value in an unsorted table

## 8. How to do a binary xlookup

This example demonstrates how to do a binary lookup using the XLOOKUP function.

Make sure the lookup_array is sorted appropriately based on the chosen value in the last argument.

Formula in cell C3:

The last argument lets you choose the search mode.

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

2 : Binary search, *lookup_array *must be sorted in ascending order.

## 9. How to do a wildcard xlookup

The image above demonstrates how to do a wildcard lookup using the XLOOKUP function.

The asterisk matches any number of characters. M* matches both Milano and Madrid, however, Milano is the first match in the list. The corresponding value is "C" and that is returned in cell F3.

Formula in cell C3:

The second last argument lets you choose the match mode.

*lookup_value*, *lookup_array*, *return_array*, [*if_not_found*], [*match_mode*], [*search_mode*])

* - Any number of characters.

? - Any single character.

~ (tilde) followed by ?, *, or ~

## 11. Search values distributed horizontally and return corresponding values

**Question:** Hi, The formula here works great but I can't figure out how to change it to work with data in columns.

Here is what I have:

=INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN()))

A B C D E

1 A B A C D

2 Car Bus Aeroplane Rocket Ship

3 A

I'd expect the result to read:

A B

4 Car Aeroplane

...but instead I get

A B

4 #NUM #NUM

Can you offer any advice?

*This is a question from Using array formula to look up multiple values in a list*

**Answer:**

Array formula in cell B8:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B8

#### Step 1 - Check if lookup value is equal to values in cell range C2:I2

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). The following lines explain the logical expression:

$C$2:$I$2=$C$5

becomes

{2012,2008,2011,2012,2014,2013,2012}=2012

and returns

{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}

#### Step 2 - Return corresponding column number

The column number will help us identify the values we want to return from another row. TRUE - corresponding column number, FALSE - nothing "".

IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), "")

becomes

IF({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), "")

becomes

IF({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}, {1,2,3,4,5,6,7}, "")

and returns

{1,"","",4,"","",7}.

#### Step 3 - Extract k-th smallest column number

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), ""), ROWS($A$1:A1))

becomes

SMALL({1,"","",4,"","",7}, ROWS($A$1:A1))

becomes

SMALL({1,"","",4,"","",7}, 1)

and returns 1.

#### Step 4 - Return value based on column number

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($C$3:$I$3, SMALL(IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), ""), ROWS($A$1:A1)))

becomes

INDEX($C$3:$I$3, 1)

becomes

INDEX({140,200,670,510,200,690,170}, 1)

and returns 140 in cell B8.

## 12. Filter values distributed horizontally - Excel 365

The FILTER function is capable to filter values arranged horizontally as well, the TRANSPOSE function rearranges the result vertically.

Excel 365 formula in cell B8:

### Explaining formula

#### Step 1 - Logical test

The equal sign is a logical operator, it allows you to compare value to value. The result is a boolean value TRUE or FALSE.

C2:I2=C5

becomes

{2012,2008,2011,2012,2014,2013,2012}=2012

and returns

{TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE}.

#### Step 2 - Filter values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(C3:I3,C2:I2=C5)

becomes

FILTER({140, 200, 670, 510, 200, 690, 170},{TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE})

and returns

{140, 510, 170}.

#### Step 3 - Transpose values

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(FILTER(C3:I3,C2:I2=C5))

becomes

TRANSPOSE({140, 510, 170})

and returns

{140; 510; 170}.

### Useful links

XLOOKUP function - Microsoft

How To Use the XLOOKUP Function in Excel

### 'XLOOKUP' function examples

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

### Functions in 'Lookup and reference' category

The XLOOKUP function function is one of 25 functions in the 'Lookup and reference' category.

### Excel function categories

### Excel categories

### 10 Responses to “How to use the XLOOKUP function”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

That works fantastically! Thanks very much. A great way to start on a Monday morning!

i have two colums

department NO

sales 2

computers 1

laptops 1

books 2

i am doing lookup but

getting result is

2 books - i should get "sales" here

2 books

1 laptops - i should get "computers" here

1 laptops

Could you please help me out

rave,

Read this post: How to return multiple values using vlookup

=INDEX($A$2:$E$2, SMALL(IF($A$1:$E$1=$A$3, COLUMN($A$1:$E$1)-MIN(COLUMN($A$1:$E$1))+1, ""), COLUMNS($A:A)) + CTRL + SHIFT + ENTER

Works great, however if the criteria is not in the table, I need the cell to be blank. E.g ISNA for a vlookup etc excel2003.

Thanks

Ross,

=IFERROR(INDEX($A$2:$E$2, SMALL(IF($A$1:$E$1=$A$3, COLUMN($A$1:$E$1)-MIN(COLUMN($A$1:$E$1))+1, ""), COLUMNS($A:A)), "")

I'm having trouble with a formula. I need it to look at another sheet within the same workbook, and pull information. My sheet looks like this:

Last Name First Name Grade TCH Status Required Class CH

Thomas John 7 New 12 PHN01 10

It goes on to list 7 more Class and CH columns. Teachers have signed up for classes and I have a spreadsheet with their choices. I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up on. I'm hoping this can be done automatically...:)

Nancy,

Can you explain in greater detail?

I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up onDo you want multiple drop down list containing all the teachers last and first names?

I have a table of dates C32:I82. Each column represents a different type of day off. However, all dates in the range are included in total days off. I want to extract all dates from the range that fall between a start and an end date and store it in another range. I am having great difficulty with this as I am a novice at best.

Wil R.

However, all dates in the range are included in total days offCan you explain in greater detail?

i have below table

name 12/1/2020|13/1/2020|14/1/2020|Firs date| second date| third Date

A 1 | | 1 | | |

B | 1 | 1 | | |

and i want formula to get Firs date, second date and third date if its available.