XLOOKUP Patterns Cheat Sheet With Real Examples
Build an XLOOKUP cheat sheet for your own data, covering two-way lookups, multiple criteria, wildcard search, and returning whole rows.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Create an XLOOKUP cheat sheet based on my own data so the examples feel real. My spreadsheet app is [SPREADSHEET_APP] and my version is [APP_VERSION]. My main table has these headers in row 1: [COLUMN_HEADERS]. Typical lookups I do: [LOOKUP_TASKS]. A few sample rows (invented or anonymised): [SAMPLE_ROWS]. Write the sheet as a table with columns: Task, Formula to paste, How it works in one line, Common error. Include at least these patterns, adapted to my headers: basic exact lookup; lookup with a friendly "not found" message; lookup to the left; return several columns with one formula; lookup with two criteria by combining columns; wildcard match for partial text; nearest smaller value for tiered rates; two-way lookup (row and column); last match instead of first. Under the table, write a "when XLOOKUP is the wrong tool" paragraph (for example, when INDEX and MATCH or FILTER fits better) and a 5 point troubleshooting list for errors like #N/A, #VALUE! and spill errors. Rules: make formulas use my real header names where possible and show cell references clearly. Do not claim an Excel or Sheets feature works unless it does; mark version or plan dependent features [VERIFY]. If a pattern is not possible with my data layout, say so and suggest a helper column. Keep the whole answer under 600 words apart from the table.
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.