XLOOKUP Excel Column Index: What Replaces It?

Formulas & functions · Updated November 18, 2026

XLOOKUP does not use a column index number. Instead, you give it the range or array to search and then directly give it the range or array to return a result from. That means you do not count columns as you do with VLOOKUP; you select the return range itself.

Why XLOOKUP Has No Column Index Number

In VLOOKUP, the third argument is a number that identifies which column in a table contains the result. For example, 2 means the second column of the selected table, while 4 means the fourth. This can be fragile: if someone inserts a column into the table, the number may no longer point to the intended field.

XLOOKUP uses a different design. Its second argument identifies where to look, and its third argument identifies what to return. Because you specify the return range directly, there is no need for a column-index argument. This also means the lookup range and return range do not have to be the leftmost and rightmost parts of one table, or even sit next to each other.

For example, you can search a product-code column and return a price column that appears to its left. You can also search one worksheet and return a value from another worksheet, as long as the two ranges line up in the relevant direction.

How XLOOKUP’s Arguments Work

The function’s syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The square brackets in this syntax indicate optional arguments; do not type the brackets into a formula. The first three arguments are the essentials:

  • lookup_value: The value to find, such as a product code in a cell.
  • lookup_array: The row or column Excel searches for that value.
  • return_array: The corresponding row, column, or range from which Excel returns the answer.

The optional if_not_found argument lets you choose a message or other result when there is no match. The default match is exact. The optional match_mode changes the matching rule, and search_mode controls the direction or method of searching. Most everyday lookups need only the first three arguments, sometimes with a not-found message.

A Basic XLOOKUP Example

Suppose A2:A5 contains product codes P-101, P-102, P-103, and P-104; B2:B5 contains product names; and C2:C5 contains prices of 12.00, 7.50, 18.50, and 4.25, respectively. To find the price for P-103, search the code range and return the corresponding value from the price range.

With those values in A2:C5, this formula returns 18.50:

=XLOOKUP("P-103",A2:A5,C2:C5,"Not found")

The fourth argument is a custom result for a missing code. If P-103 were absent from A2:A5, the formula would display Not found instead of the default #N/A error. In a working sheet, the lookup value is often a cell reference rather than text typed into the formula. For example, if G2 contains P-103, use:

With the lookup code in G2, codes in A2:A5, and prices in C2:C5, this returns 18.50:

=XLOOKUP(G2,A2:A5,C2:C5,"Not found")

Using a cell reference makes the result update when the selected code changes. It also avoids editing the formula each time you want to look up a different item.

Choosing the Return Range Instead of Counting Columns

To choose a return range, first identify the field you want as the answer, then select the cells containing that field in the same order as the lookup values. If product codes are in A2:A100 and the matching supplier names are in D2:D100, select D2:D100 as the return array. It does not matter that the supplier column is the fourth column of the worksheet: XLOOKUP is not asking for a worksheet column number.

With the code to find in G2, product codes in A2:A100, and corresponding suppliers in D2:D100, this returns the matching supplier:

=XLOOKUP(G2,A2:A100,D2:D100,"No match")

Keep the ranges aligned. In a vertical lookup, each item in the lookup column should correspond to the item on the same row in the return column. If the lookup array has 99 cells but the return array starts or ends on different rows, the formula may return the wrong record or produce an error. The two ranges can be in different columns or sheets, but their corresponding positions must represent the same records.

Returning More Than One Column

A useful difference from a traditional column-index lookup is that XLOOKUP can return several adjacent columns from the matched row. For instance, you can retrieve a product name, category, and price together rather than writing a separate lookup formula for each field.

Suppose product codes are in A2:A5, and names, categories, and prices are in B2:D5. If G2 contains a code, this formula returns the matching cells from B:D:

With the lookup value in G2, codes in A2:A5, and the three return fields in B2:D5, the result spills into three cells across:

=XLOOKUP(G2,A2:A5,B2:D5,"Not found")

In Microsoft 365, the returned values spill into neighboring cells automatically. Those cells must be empty; otherwise, Excel displays a #SPILL! error because it cannot place the full result. Clear the obstructing cells or move the formula. This multi-column result is one reason XLOOKUP does not need a column index: the return array can be the exact field or set of fields required.

Replacing a VLOOKUP Column Number

When converting an existing VLOOKUP, identify its lookup value, the column being searched, and the column containing the intended answer. Use the lookup column as XLOOKUP’s second argument and the answer column as its third. Do not copy the old VLOOKUP column number into XLOOKUP; the functions use different argument structures.

For example, if a VLOOKUP searches codes in A2:D100 and its column number is 4, its intended return field is column D. With the code in G2, codes in A2:A100, and the desired values in D2:D100, the equivalent XLOOKUP is:

=XLOOKUP(G2,A2:A100,D2:D100,"Not found")

The explicit ranges make the purpose easier to read. They also allow the return field to be left of the lookup field, something VLOOKUP cannot do with its standard left-to-right lookup arrangement. When translating formulas, check that the selected return range really corresponds to the old column number and that any approximate-match behavior is intentionally preserved or changed.

Exact, Approximate, and Reverse Searches

XLOOKUP uses exact matching by default, which suits IDs, names, and other values expected to match precisely. For approximate matching, the fifth argument sets match_mode: 0 is exact, -1 is exact or next smaller, 1 is exact or next larger, and 2 enables wildcard matching. Approximate matches are useful for ordered thresholds, such as assigning a rate from a band of values, but the threshold data must be arranged appropriately for the chosen rule.

Suppose A2:A5 contains ascending minimum scores 0, 60, 70, and 80, and B2:B5 contains the corresponding grade labels Fail, C, B, and A. With a score in E2, this formula returns the label for an exact score or the next smaller threshold. A score of 74 returns B:

=XLOOKUP(E2,A2:A5,B2:B5,"No band",-1)

The sixth argument, search_mode, can make Excel search from last to first. This helps when a list contains repeated keys and the latest matching entry is wanted. With IDs in A2:A20, dates in B2:B20, results in C2:C20, and the ID to find in G2, this returns the result from the last matching row:

=XLOOKUP(G2,A2:A20,C2:C20,"Not found",0,-1)

Reverse search does not sort the data; it simply changes the direction of the search. Be clear about what “last” means in your worksheet, especially if rows can be sorted or new records are added in the middle.

Common Errors and How to Fix Them

  • #N/A: No exact match was found, or the lookup value differs from the stored value. Check for extra spaces, inconsistent data types, or a misspelled key. Add an if_not_found result if a friendlier message is useful.
  • #VALUE!: The lookup and return arrays may have incompatible dimensions. For a vertical lookup, make sure the return range has the same number of rows as the lookup range.
  • #SPILL!: A formula returning several columns cannot place its results because cells in the spill area are occupied, merged, or otherwise unavailable. Make room for the output.
  • Unexpected result: Duplicate lookup values are another possibility. By default, XLOOKUP searches from first to last and returns the first match. Use reverse search if the last match is the one you need.
  • Function name not recognized: The Excel version may not include XLOOKUP, or the formula may have been opened in an older version. Check the version and the compatibility needs of anyone sharing the workbook.

Also check whether numbers are stored as text in one range but as numbers in another. They can look identical while failing to match. A quick way to diagnose this is to inspect the source cells and standardize the data type in the source data rather than masking the problem with increasingly complicated formulas.

Availability and Excel Versions

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also available in Excel for the web and supported current Excel for Mac versions. The formula’s core arguments work the same across these editions, although menus and interface details can differ. This guidance focuses on Excel for Microsoft 365 on Windows.

Excel 2019 and earlier do not include XLOOKUP. A workbook containing the function may not calculate correctly for someone using an unsupported version. If the workbook must work in older editions, consider a compatible alternative such as INDEX with MATCH, or retain a VLOOKUP formula where its left-to-right layout and other limitations are acceptable. Ask collaborators what versions they use before replacing shared formulas.

XLOOKUP Compared with INDEX and MATCH

INDEX and MATCH can perform flexible lookups in older Excel versions, including lookups where the return column is to the left of the search column. XLOOKUP combines the lookup and return selection into one function and includes built-in options for not-found results, match modes, and reverse searches. That can make a formula easier to maintain, particularly when the return range is named clearly or the worksheet has several lookup fields.

VLOOKUP remains familiar and may be necessary for compatibility, but its column-number argument can be harder to maintain when table layouts change. XLOOKUP avoids that counting step; it does not automatically correct poor source data or guarantee a unique match. Choosing between functions is therefore not only about brevity. Version support, duplicate keys, workbook sharing, and how easily another person can audit the formula all matter.

Frequently Asked Questions

What is the column index number in XLOOKUP?
XLOOKUP has no column index number. Its third argument is the return array, so select the range containing the result you want instead of entering a number.

How do I return a value from the third column with XLOOKUP?
Select the actual range in that column as the third argument. If the lookup values are in A2:A20 and the desired results are in C2:C20, use C2:C20 as the return array; you do not enter 3.

Can XLOOKUP look to the left?
Yes. The return array can be on either side of the lookup array. Select the range containing the answer directly, and ensure its rows or columns correspond to the lookup array.

What replaces VLOOKUP’s column index number?
The XLOOKUP return_array argument replaces the need for that number. It points directly to the cells whose matching value should be returned.

Why does my XLOOKUP return #N/A?
Usually, Excel cannot find an exact match. Verify the lookup value, remove unintended spaces, and check that numbers and text are stored consistently. You can also supply an optional not-found result.

Can XLOOKUP return multiple columns?
Yes. Use a multi-column return array, such as B2:D20, to return several fields from the matched row. In Microsoft 365, the results spill into adjacent cells, so keep the output area clear.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.