Excel XLOOKUP Function
XLOOKUP Function
The XLOOKUP function is a premade function in Excel, which searches a range and returns the matching value from another range.
It is a newer and more flexible version of VLOOKUP. XLOOKUP can look to the left, and it uses exact match by default.
It is typed =XLOOKUP and has the following parts:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Note: XLOOKUP is available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019 or earlier. In those versions, use INDEX and MATCH instead.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Lookup_value: The value to search for. This is usually the cell where the search value is entered.
Lookup_array: The range to search in, for example one column.
Return_array: The range to return a value from. When you search a column, it must have the same number of rows as the lookup_array.
If_not_found: Optional. The value to show if no match is found. If you leave it out, XLOOKUP returns #N/A.
Match_mode: Optional. How the lookup_value is matched:
| Match_mode | Description |
|---|---|
0 | Exact match. This is the default. |
-1 | Exact match. If none is found, the next smaller item. |
1 | Exact match. If none is found, the next larger item. |
2 | Wildcard match, where * and ? have a special meaning. |
Search_mode: Optional. The direction of the search:
| Search_mode | Description |
|---|---|
1 | Search from first to last. This is the default. |
-1 | Search from last to first. |
There are also search modes 2 and -2, for searching data that is sorted. You will rarely need them.
How to use the XLOOKUP function:
- Select a cell (
H4) - Type
=XLOOKUP - Double click the XLOOKUP command
- Select the cell where the search value will be entered (
H3) - Type (
,) - Mark the range to search in (
B2:B21) - Type (
,) - Mark the range to return a value from (
E2:E21) - Hit enter
- Enter a value in the search cell (
H3)
Let's have a look at an example!
This is the same Pokemon table as on the VLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
Type the labels Search Name in G3 and Total in G4.
Use the XLOOKUP function to find the Total of a Pokemon based on its Name. Type Squirtle in H3:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | |||
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | |||
| 3 | 2 | Ivysaur | Grass | Poison | 405 | Search Name | Squirtle | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | Total | 314 | |
| 5 | 4 | Charmander | Fire | 309 | ||||
| 6 | 5 | Charmeleon | Fire | 405 | ||||
| 7 | 6 | Charizard | Fire | Flying | 534 | |||
| 8 | 7 | Squirtle | Water | 314 | ||||
| 9 | 8 | Wartortle | Water | 405 | ||||
| 10 | 9 | Blastoise | Water | 530 | ||||
| 11 | 10 | Caterpie | Bug | 195 | ||||
| 12 | 11 | Metapod | Bug | 205 | ||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | |||
| 14 | 13 | Weedle | Bug | Poison | 195 | |||
| 15 | 14 | Kakuna | Bug | Poison | 205 | |||
| 16 | 15 | Beedrill | Bug | Poison | 395 | |||
| 17 | 16 | Pidgey | Normal | Flying | 251 | |||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | |||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | |||
| 20 | 19 | Rattata | Normal | 253 | ||||
| 21 | 20 | Raticate | Normal | 413 |
=XLOOKUP(H3, B2:B21, E2:E21)
XLOOKUP searches B2:B21 for Squirtle and finds it in row 8. It returns the value from the same row in E2:E21, which is 314.
There is no column number to count, and no range_lookup to set. Exact match is the default.
Try another name in H3, like Blastoise. The result changes to 530.
XLOOKUP to the Left
VLOOKUP can only return values from columns to the right of the search column. XLOOKUP can return values from any column, also columns to the left.
Find the ID# of a Pokemon based on its Name. The ID# column (A) is to the left of the Name column (B).
Change the label in G4 to ID#, and type Pidgeotto in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | Pidgeotto |
| 4 | ID# | 17 |
=XLOOKUP(H3, B2:B21, A2:A21)
XLOOKUP finds Pidgeotto in B18 and returns 17 from A18. VLOOKUP cannot do this.
If Not Found
If XLOOKUP does not find the value, it returns #N/A. Use the if_not_found part to show your own message instead.
Pikachu is not in the table. Change G4 back to Total, and type Pikachu in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | Pikachu |
| 4 | Total | Not found |
=XLOOKUP(H3, B2:B21, E2:E21, "Not found")
Without "Not found", the result would be #N/A.
Note: Text in a formula must be inside double quotes: " "
Return Several Columns
The return_array can be more than one column wide. Then XLOOKUP returns several values at once, and they spill into the cells to the right.
Return Type 1, Type 2 and Total for Charizard with one formula. Change G4 to Types and Total, and type Charizard in H3:
| G | H | I | J | |
|---|---|---|---|---|
| 3 | Search Name | Charizard | ||
| 4 | Types and Total | Fire | Flying | 534 |
=XLOOKUP(H3, B2:B21, C2:E21)
The formula is typed only in H4. The return_array C2:E21 is three columns wide, so the result spills into H4:J4.
Note: The cells that the result spills into must be empty. If they are not, XLOOKUP returns a #SPILL! error.
Now type Squirtle in H3. Squirtle has no Type 2:
| G | H | I | J | |
|---|---|---|---|---|
| 3 | Search Name | Squirtle | ||
| 4 | Types and Total | Water | 0 | 314 |
Note: When the matching cell is empty, XLOOKUP returns 0, not an empty cell. That is why I4 shows 0 for Squirtle. Keep this in mind when a column has empty cells.
Search From the Bottom
By default, XLOOKUP searches from the first row to the last, and returns the first match. Set search_mode to -1 to search from the last row up, and get the last match instead.
Find the first and the last Bug Pokemon in the table. Type the labels Search Type in G3, First in G4 and Last in G5. Then type Bug in H3:
| G | H | |
|---|---|---|
| 3 | Search Type | Bug |
| 4 | First | Caterpie |
| 5 | Last | Beedrill |
The formula in H4 searches from the top:
=XLOOKUP(H3, C2:C21, B2:B21)
The formula in H5 searches from the bottom:
=XLOOKUP(H3, C2:C21, B2:B21, "Not found", 0, -1)
The first Bug Pokemon is Caterpie (ID# 10). The last one is Beedrill (ID# 15).
To set search_mode, you must also fill in the parts before it. Here, if_not_found is "Not found" and match_mode is 0 (exact match).
VLOOKUP vs XLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Look to the left | No | Yes |
| Default match | Approximate (when range_lookup is left out) | Exact |
| The return column is set by | A column number | A range |
| Inserting a column in the table | Can break the result | Adjusts automatically |
| Own message when nothing is found | No | Yes, with if_not_found |
| Search from the bottom | No | Yes, with search_mode |
| Horizontal lookups | No, use HLOOKUP | Yes |
| Excel versions | All versions | Microsoft 365, Excel 2021, Excel 2024 and Excel for the web |
If everyone who uses your file has Excel for Microsoft 365 or Excel 2021 or newer, XLOOKUP is usually the easier choice. For older versions, use VLOOKUP or INDEX and MATCH.
