Yes. To look up a value on one Excel sheet and return a related value from another, use XLOOKUP with the lookup and return ranges prefixed by the source sheet name. If the ID to find is in A2 and the IDs and product names are on a sheet named Product List, use =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$B$2:$B$100,"Not found"). If A2 contains P-104 and that ID is paired with Insulated Bottle in the source list, the formula returns Insulated Bottle.
What XLOOKUP Between Sheets Does
XLOOKUP searches a range for a value and returns a corresponding value from another range. The lookup range and return range can be on a different worksheet from the cell containing the formula. This is useful when one sheet holds a transaction, order, or staff list while another sheet contains the reference information you need to add.
For example, an Orders sheet might contain product IDs but not product names. A Product List sheet can hold the IDs alongside their names. The formula searches the ID column on Product List for the ID from the current order, then returns the name from the same matched row. The formula does not move or copy the source data; it displays a result linked to the source range.
XLOOKUP uses an exact match by default, which is usually what you want for IDs, names, and codes. If the source value changes, the returned value can update when Excel recalculates. The result is only as reliable as the data: spelling differences, extra spaces, duplicate IDs, or numbers stored as text can affect the match.
How the Formula Is Put Together
The basic order of XLOOKUP arguments is the value to find, the range to search, the range to return a result from, and, optionally, what to show when no match exists. In the example above, A2 is the value to find; the Product List column A range is searched; and the corresponding cell in column B is returned. The optional Not found text replaces the usual not-found error with a more readable message.
In a worksheet reference, an exclamation mark separates the sheet name from its cell range. A sheet name containing a space is enclosed in single quotation marks, as in ‘Product List’!A2:A100. A simple sheet name without spaces can be written without those quotation marks. Excel often adds the required marks automatically when you select a range on another sheet while editing a formula.
The dollar signs in $A$2:$A$100 and $B$2:$B$100 make those ranges absolute. If you copy the formula down, the source ranges stay fixed while A2 changes to A3, A4, and so on. The lookup value is intentionally relative so each row can search for its own ID.
Set Up a Lookup Across Worksheets
- Put the value you want to search for on the worksheet where you need the answer. For example, place an order ID in A2 on Orders.
- Confirm that the other worksheet has a lookup column and a result column. Keep each ID paired with the correct name or other detail on the same row.
- Select the result cell on Orders, type the XLOOKUP formula, and use the source sheet name before both ranges.
- Press Enter and check the returned value against the source row. If the formula will be used for more records, fill or copy it down the result column.
You can also build the references by typing the opening portion of the formula, switching to the source sheet, and selecting the cells with the mouse. Excel inserts the sheet reference into the formula. This is often easier than manually typing a long sheet name, and it reduces mistakes in punctuation.
For a maintainable workbook, avoid selecting entire columns when a bounded range or Excel Table is suitable, especially in a large workbook with many formulas. Keep the lookup and return ranges the same height and make sure the source list includes every row you expect to search. If new rows are regularly added, an Excel Table can make the source easier to expand and manage.
Example: Return a Different Field
Suppose Orders has an ID in A2, while Product List has product IDs in A2:A100 and product names in B2:B100. The opening formula returns the product name. To retrieve a price instead, point the return range to the price column, assuming prices are in column C.
With the lookup value P-104 in Orders!A2, Product List IDs in A2:A100, and prices in C2:C100, use =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$C$2:$C$100,"Price missing"). If P-104 is on row 8 of Product List and C8 contains 24.99, the result is 24.99. The actual position of the matching ID does not need to be the same as the row number of the value in Orders; XLOOKUP finds the matching source row.
XLOOKUP can also return more than one adjacent field from a single match. With P-104 in Orders!A2, product IDs in Product List!A2:A100, names in B, and categories in C, use =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$B$2:$C$100,"Not found"). If the matching source row contains Insulated Bottle in B and Drinkware in C, those two results spill into the formula cell and the cell immediately to its right. Keep that spill area empty; an occupied cell can produce a #SPILL! error.
Choose What Happens When There Is No Match
If you leave out XLOOKUP’s optional not-found argument, a missing match returns #N/A. Giving the formula a message such as Not found can make a report easier to read, but it can also conceal a data problem if readers mistake the message for a legitimate category. Choose wording that makes it clear the value was not matched.
With lookup value P-999 in Orders!A2, IDs in Product List!A2:A100, and names in Product List!B2:B100, use =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$B$2:$B$100,""). If P-999 is absent, the result is a blank-looking cell. That can be useful for presentation, but it does not explain why the result is blank; a specific message may be better during checking.
Exact matching is the default, so you normally do not need to add a match-mode argument for IDs. XLOOKUP also supports approximate matching. For example, an ascending list of score thresholds can assign a band based on the closest threshold that is less than or equal to a score.
With a score of 82 in A2, thresholds 0, 60, 80, and 90 in D2:D5, and their labels in E2:E5, use =XLOOKUP(A2,$D$2:$D$5,$E$2:$E$5,"No band",-1). If the labels are Starter, Bronze, Silver, and Gold in that order, the formula returns Silver because 80 is the greatest threshold not exceeding 82. The -1 match mode means exact match or next smaller item; it is not the same as the default exact match.
Common Errors and How to Fix Them
- #N/A: The lookup value was not found when no custom not-found result was supplied. Check spelling, spaces, and whether the value exists in the lookup range. Also check whether one side is stored as text and the other as a number.
- #VALUE!: The lookup and return arrays may have incompatible dimensions. For a vertical lookup, use ranges with the same number of rows; for a horizontal lookup, use ranges with the same number of columns.
- #SPILL!: A formula returning multiple columns or rows cannot place all its results because cells in the spill area are occupied. Clear those cells or return only one field.
- #REF!: A sheet or range reference may have become invalid, for example after a referenced worksheet was deleted. Check the sheet name and source range.
- #NAME?: The Excel version may not support XLOOKUP, or the function name may be misspelled. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, but not in some older Excel releases.
Duplicate lookup values are another important case. By default, XLOOKUP searches from the first item to the last and returns the first match. If an ID appears more than once, the returned result may therefore come from the earlier duplicate, not the one you intended. Correct the duplicate data or deliberately choose a search direction when the last match is required.
Excel formulas shown here use commas between arguments. In some regional settings, Excel expects semicolons instead; if Excel rejects a pasted formula, check the list separator used by your installation. The function and worksheet reference logic remain the same.
XLOOKUP Compared with VLOOKUP
VLOOKUP can also search a table and return a related value, but its traditional setup requires a column index number, and it generally searches from the leftmost column of the selected table toward columns to its right. XLOOKUP names the lookup and return ranges separately, so the return column can be either to the left or the right of the lookup column. This can make a formula easier to understand when the source layout changes.
XLOOKUP also has a built-in not-found argument and uses exact match by default. With VLOOKUP, users commonly need to specify exact-match behavior explicitly and may use IFERROR or IFNA to customize missing-result handling. Neither function fixes inconsistent source data, and both can return misleading results if the lookup key is not unique.
For a workbook shared with people using older Excel versions, compatibility matters more than convenience. If recipients use an Excel version without XLOOKUP, the formula may display #NAME? for them. Check their version or use a compatible alternative before distributing the workbook.
Frequently Asked Questions
Can XLOOKUP search another sheet in the same workbook?
Yes. Put the source sheet name and an exclamation mark before the lookup and return ranges. Enclose sheet names containing spaces in single quotation marks, such as ‘Product List’!A2:A100.
Do I need to use an exact-match setting?
No. Exact match is XLOOKUP’s default. For ordinary IDs, names, and codes, leaving the match-mode argument out is generally appropriate. Use an approximate mode only when the data and intended matching rule call for it.
Can XLOOKUP return several columns at once?
Yes. Use a multi-column return range, such as B2:C100, while searching a single matching column. In Microsoft 365 and supported newer Excel versions, the results spill into adjacent cells; make sure those cells are clear.
Why does my cross-sheet XLOOKUP return #N/A?
The value may genuinely be absent, or it may differ because of extra spaces, misspellings, or a text-versus-number mismatch. Confirm that the lookup range points to the intended sheet and column, then compare the lookup value directly with the source value.
Does XLOOKUP work in Excel for Mac and Excel for the web?
XLOOKUP is available in supported Microsoft 365 versions on Windows, Mac, and the web, and the basic cross-sheet formula structure is the same. Ribbon layout and some editing details can differ, but a worksheet reference still uses the sheet name, an exclamation mark, and the cell range.
Related
- Excel Data Analysis Skills: What They Include and Why
- Excel Solver for Data Analysis: Setup, Models, and Limits
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS Greater Than and Less Than: ExamplesYes. In Excel, use greater-than and less-than operators directly inside an IFS logical test, such as A2>10 or…
- Excel ISBLANK Function Example: Formulas and TipsIn Excel, use =ISBLANK(A2) to test whether cell A2 is genuinely empty; it returns TRUE if the cell…
- Excel PivotChart: How to Count Unique ValuesYes. In Excel for Microsoft 365 on Windows, you can count unique values in a PivotChart by building…
- XLOOKUP Advanced Examples for Real-World Excel TasksXLOOKUP advanced examples include returning several columns at once, matching on multiple conditions, finding the last occurrence, and…
- Sort Excel Column Without a Header Safely in ExcelTo sort an Excel column that has no header, select the data cells, then choose Data > Sort…
- Excel Consolidate Text Data: Formulas and Power QueryTo consolidate text in Excel, join cells with TEXTJOIN or combine rows from multiple tables with Power Query.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.