Organize information

Build an XLOOKUP between Excel sheets

Join two sheets with an explicit key, match rule, and no-match behavior

5 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Target sheetSource sheetMatch and exception rules

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Create an Excel XLOOKUP for these sheets.

Target sheet:
[target]

Source sheet:
[source]

Match and exception rules:
[rules]

Before writing the formula, confirm the lookup key, return field, exact-versus-approximate match, and whether the source key is unique. Do not silently choose the first duplicate. Return:
1. Formula for the first target row
2. Explanation of every XLOOKUP argument
3. Blank and no-match behavior
4. Checks for text/number mismatch, whitespace, and duplicate keys
5. Four test cases and expected results
6. A structured-reference version if both ranges are Excel Tables.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Target sheet: Orders sheet: A is Order ID, B is Customer ID, and F should contain Customer Segment. Data starts at row 2.
Source sheet: Customers sheet: A contains unique Customer ID values and D contains Customer Segment. Headers are in row 1.
Match and exception rules: Use exact matching. Blank Customer ID should return blank. Unknown IDs should display 'Review'. Duplicate IDs in Customers are data errors and must be reported rather than silently accepted.

Example output

Confirmed mapping: Orders!B2 is the lookup value; Customers!A:A is the lookup array; Customers!D:D is the return array. Customer ID must be unique and the match is exact.

Formula for Orders!F2 =IF(B2="","",XLOOKUP(B2,Customers!$A:$A,Customers!$D:$D,"Review",0))

The outer IF preserves blank input rows. XLOOKUP searches B2 in the fixed customer-ID column, returns the same row from column D, displays “Review” when no match exists, and uses match mode 0 for exact matching. The dollar signs keep source columns fixed when copied.

Data checks: Compare ISTEXT/ISNUMBER on both ID columns; test trimmed lengths; use =COUNTIF(Customers!$A:$A,A2) in a helper column and investigate results above 1.

Tests: known ID → stated segment; blank ID → blank; unknown ID → Review; duplicate source ID → flag in the helper check before trusting the lookup.

Table version: =IF([@[Customer ID]]="","",XLOOKUP([@[Customer ID]],Customers[Customer ID],Customers[Customer Segment],"Review",0)).

Why this works

1
Naming both sides of the join prevents swapped lookup and return arrays.
2
Duplicate-key checks prevent a syntactically valid formula from returning an arbitrary record.