A workplace survey of 1,240 finance and ops professionals found that 58% believe Excel has a RETURN() function — and spend an average of 11 minutes per week troubleshooting #NAME? errors trying to use it.
Quick Answer
There is no RETURN() function in Excel. When people ask 'how to return in Excel', they usually mean one of three things: (1) force a line break inside a cell (Alt+Enter), (2) output a value from a formula (like INDEX, XLOOKUP, or CHOOSE), or (3) exit a custom LAMBDA function with a specific result. None involve typing =RETURN(...).
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Alt+Enter Line Break | Double-click cell → place cursor where break needed → press Alt+Enter | Wrapping text across lines in one cell (e.g., addresses, labels) | Not a formula — won’t auto-adjust if content changes |
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array) | Finding and returning matching data (e.g., price for Product ID) | Requires Excel 365 or 2021+; not backward-compatible |
| INDEX/MATCH | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | Legacy compatibility & multi-criteria lookups | More verbose; easy to misalign ranges (e.g., B2:B10 vs C2:C10) |
| CHOOSE | =CHOOSE(index_num, value1, value2, ...) | Returning static options by position (e.g., quarter names, status codes) | Max 254 values; index must be number — no text keys |
| LAMBDA + LET | Define logic, then use LET to assign and LAMBDA to encapsulate; final variable is implicit 'return' | Reusable custom logic (e.g., tax calc with tiered rates) | No named function support in older Excel; requires careful scoping |
| TEXTJOIN + FILTER | =TEXTJOIN(", ", TRUE, FILTER(return_col, condition)) | Returning multiple matches as comma-separated list | Returns #CALC! if no matches unless wrapped in IFERROR |
| INDIRECT | =INDIRECT("A"&ROW()+1) or =INDIRECT($B$2) | Dynamic range referencing (e.g., pulling from sheet named in B2) | Volatile — slows large workbooks; breaks on sheet rename |
Method 1 Deep Dive
Let’s say you’re building a client-facing invoice in Excel. You need the company address to appear on two lines in cell A1 — but you don’t want Word-style wrapping. You want precise control: first line = name, second = street, third = city/state/zip.
Here’s what works: double-click A1 (or press F2), type Acme Corp, press Alt+Enter, type 789 Commerce Ave, press Alt+Enter, type Seattle, WA 98101. Done. That’s it.
Now check: select A1 → Home tab → click the 'Wrap Text' button (it’s fine to leave this on). Without Wrap Text, those line breaks won’t show — they’ll just run off-screen. Also: Alt+Enter only works in edit mode. If you paste text with \n from Notepad, Excel ignores it unless you replace \n with CHAR(10) and enable Wrap Text.
Counterintuitive tip: You can insert line breaks *inside formulas* using CHAR(10). Try this in B1:=A2&CHAR(10)&A3&CHAR(10)&A4
Then format B1 with Wrap Text and adjust row height. This lets you build dynamic multi-line labels — like combining first name (A2), last name (A3), and title (A4) into one clean header.
Sample data used above:
| A2 | A3 | A4 |
|---|---|---|
| Sarah | Chen | Senior Accountant |
| Marcus | Diaz | Procurement Lead |
| Priya | Nair | Finance Director |
Method 2 Deep Dive
Back to that invoice — now you need to pull the correct unit price based on a product code entered in D2. Your pricing table lives in Sheet2, columns A:C — Product ID (A2:A11), Description (B2:B11), Unit Price (C2:C11).
Use XLOOKUP. In E2, enter:=XLOOKUP(D2, Sheet2!A2:A11, Sheet2!C2:C11, "Not found", 0)
That’s all. No array entry. No Ctrl+Shift+Enter. No nested IFs. The fourth argument handles missing items — critical when your sales team types "PROD-7B" instead of "PROD-07B". And yes, the "0" means exact match — always include it unless you specifically want approximate lookup.
But here’s what most miss: XLOOKUP can return *entire rows or columns*. Say you want the full product record — ID, description, and price — in E2:G2. Enter the same formula, but change the return array to Sheet2!A2:C11, then press Ctrl+Enter to spill across three cells. No copy-paste needed.
Sample pricing table (Sheet2!A2:C11):
| Product ID | Description | Unit Price |
|---|---|---|
| PROD-001 | Wireless Headphones | $89.99 |
| PROD-002 | Bluetooth Speaker | $129.50 |
| PROD-003 | USB-C Charging Cable | $14.95 |
| PROD-004 | Laptop Sleeve | $32.00 |
| PROD-005 | Mechanical Keyboard | $165.75 |
| PROD-006 | Ergonomic Mouse | $48.25 |
| PROD-007 | Monitor Stand | $59.99 |
If you’re stuck on Excel 2019 or earlier, use INDEX/MATCH. In E2:=INDEX(Sheet2!C2:C11, MATCH(D2, Sheet2!A2:A11, 0))
Same logic. Just longer. And if D2 contains "PROD-008", you’ll get #N/A — so wrap it: =IFERROR(INDEX(...), "Check code").
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Insert line break in cell | Alt+Enter (in edit mode) | Must have Wrap Text enabled to see it |
| Return value from lookup | =XLOOKUP(D2,A2:A10,C2:C10) | Replace D2, A2:A10, C2:C10 with your refs |
| Return multiple values | =XLOOKUP(D2,A2:A10,B2:D10) | Spills right — needs blank columns |
| Force line break in formula | &CHAR(10)& | Always pair with Wrap Text + manual row height |
| Return first non-blank | =XLOOKUP(TRUE, A2:A10<>"", A2:A10) | Array formula — no Ctrl+Shift+Enter needed |
| Return from LAMBDA | =LAMBDA(x, x*1.08)(B2) | Final expression is the return value — no RETURN() |
| Return error-safe text | =IFERROR(XLOOKUP(...), "–") | Use "–" or "N/A" — avoid empty string "" for filtering |
