Create beginner Works with: Any AI

XLOOKUP Formula Builder With Worked Examples

Few shot Has example

Write lookup formulas that survive real data.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Write a lookup formula for my spreadsheet. App and version: [SPREADSHEET_APP_AND_VERSION]. Layout: lookup table on sheet [LOOKUP_SHEET] in range [LOOKUP_RANGE] with the key in column [KEY_COLUMN] and the value I want in column [RETURN_COLUMN]; the cell with the value to find is [INPUT_CELL]. What should happen when nothing matches: [NOT_FOUND_BEHAVIOR]. Sample rows (fake data): [SAMPLE_ROWS].

Study how a good answer looks.
Need: find the price for the product code in A2 from Products!A:C, price in column C.
Formula: =XLOOKUP(TRIM(A2), Products!A:A, Products!C:C, "Not found", 0)
Explanation: TRIM removes stray spaces; the first range is searched; the third argument is the result range; "Not found" is shown when there is no match; 0 means exact match.
Variation for older versions: =IFERROR(INDEX(Products!C:C, MATCH(TRIM(A2), Products!A:A, 0)), "Not found").

For my layout, give: the formula; the explanation of each argument; the fallback for versions that do not support the function; a version that returns several columns at once; how to look up from the last match; a version that matches partial text with wildcards; three test cases with expected results; and the common causes of wrong results (spaces, numbers stored as text, duplicates). Warn me about whole-column ranges on large sheets.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Need: find the price for the code in A2 from Products!A:C (price in column C).
Formula: =XLOOKUP(TRIM(A2), Products!A:A, Products!C:C, "Not found", 0)
Explanation: TRIM removes stray spaces; first range is searched; third argument is the result range; "Not found" shows when no match; 0 means exact match.
Older versions: =IFERROR(INDEX(Products!C:C, MATCH(TRIM(A2), Products!A:A, 0)), "Not found")
Several columns: =XLOOKUP(TRIM(A2), Products!A:A, Products!B:D) spills three values.
Test: "AB-104 " (trailing space) returns 19.90; "ZZ-999" returns "Not found". Avoid whole-column ranges on large sheets.

• xlookup