Build a spreadsheet formula
Uses your actual sheet layout, gives a version fallback, and states how it behaves on blanks and errors.
| Category | Office work › Spreadsheets |
|---|---|
| Tags | DraftingOffice workerSpreadsheet |
Write the spreadsheet formula. Rules: 1. **Use the actual columns and rows from the sheet structure I gave you.** Do not invent placeholder references like A1 or B2. 2. Give the formula, then one line on what it does. 3. If a function may be unavailable in my version, give a fallback formula alongside. 4. State in one line how it behaves with blanks, error values, and duplicates. 5. **If the formula gets long, also propose using a helper column.** *A single-line formula is not always better and is much harder to debug.* 6. Where my description is ambiguous enough that the formula could go several ways, **ask instead of choosing.**
After pasting, fill in the fields at the bottom (Excel version · Sheet structure · What I need)
What this prompt does
Formulas returned against invented cell references do not work in your sheet. This uses your structure and offers a helper column when one formula would be unmaintainable.
Real outputs by model
Read full answers
We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.
Excel version: Microsoft 365 (구독형 최신 버전) Sheet structure: Sheet1: A열 주문번호, B열 상품명, C열 수량, D열 단가, E열 할인율, F열 지점명 / Sheet2: A열 지점명, B열 지점장명 What I need: Sheet1의 F열 지점명을 Sheet2에서 찾아 지점장명을 Sheet1의 G열에 표시하고, 지점명이 Sheet2에 없으면 '미등록지점'이라고 표시
G2에 입력 후 아래로 채우세요.
=IF(F2="","",XLOOKUP(F2,Sheet2!$A$2:$A$1048576,Sheet2!$B$2:$B$1048576,"미등록지점"))
Sheet1의 F2 지점명을 Sheet2에서 찾아 지점장명을 반환하고, 없으면 미등록지점을 표시합니다.
- 호환성: Microsoft 365 최신 버전에서는
XLOOKUP을 사용할 수 있습니다. 구버전용 fallback:
=IF(F2="","",IFERROR(INDEX(Sheet2!$B$2:$B$1048576,MATCH(F2,Sheet2!$A$2:$A$1048576,0)),"미등록지점"))
- 빈 지점명은 빈칸으로 두고, 조회·반환 범위의 오류 값은 오류로 표시하며, Sheet2에 같은 지점명이 여러 개면 첫 번째 일치 항목을 반환합니다.