Build an XLOOKUP between Excel sheets

작성자: AILesson5 분 소요테스트::ChatGPT검토일: 2026-08-28

빠른 답변

Join two sheets with an explicit key, match rule, and no-match behavior. 제공할 내용: Target sheet, Source sheet, Match and exception rules. 예상 결과: A verified lookup formula plus duplicate and data-quality checks.

맥락 추가

텍스트는 이 브라우저에 유지됩니다. AILesson Prompts는 이를 모델이나 서버로 보내지 않습니다.

Target sheetSource sheetMatch and exception rules

프롬프트

채워지지 않은 필드는 플레이스홀더로 표시되므로 프롬프트를 복사하고 편집할 수 있습니다

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.

Playground에서 사용해 보기

기본적으로 비공개프롬프트 구성은 브라우저에서 로컬로 이루어집니다. 조직에서 허용하지 않는 한 기밀 정보를 AI 서비스에 입력하지 마세요.

입력에서 결과까지

적용 예시

구체적인 맥락이 이 레시피를 바로 사용할 수 있는 결과로 바꾸는 방법을 확인하세요

실제 입력

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.

예시 출력

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)).

효과가 있는 이유

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.

결과 확인

Is the source key unique for the intended business meaning?
Are lookup values and source keys stored using the same type and normalization?
Does a known no-match row return the intended visible result?

안심하고 사용하세요

자주 묻는 질문

이 레시피를 언제 사용해야 하는지, 무엇을 제공해야 하는지, 그리고 어떤 부분에서 사람의 검토가 여전히 중요한지에 대한 실용적인 답변

What should I prepare before using “Build an XLOOKUP between Excel sheets”?

For “Build an XLOOKUP between Excel sheets,” prepare Target sheet, Source sheet, and Match and exception rules. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Build an XLOOKUP between Excel sheets” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A verified lookup formula plus duplicate and data-quality checks—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Build an XLOOKUP between Excel sheets”?

The published test record for “Build an XLOOKUP between Excel sheets” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

더 많은 탐색 방법