XLOOKUP Return Multiple Values

What this does

XLOOKUP replaces VLOOKUP and HLOOKUP with one function that looks left or right, top or bottom, and defaults to an exact match instead of an approximate one. You give it the value to find, the column to search, and the column to return — no column-index counting, no FALSE flag to remember. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “xlookup return multiple values” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Prices sit in B2:B100 keyed by SKU in A2:A100. To get the price for the SKU in cell E2: =XLOOKUP(E2, A2:A100, B2:B100). If the SKU is missing it returns #N/A; add a fourth argument to show text instead: =XLOOKUP(E2, A2:A100, B2:B100, "not found"). Reach for XLOOKUP whenever you would have written a VLOOKUP or an INDEX/MATCH: it is shorter, survives inserted columns, and fails gracefully with if_not_found. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. If you take one thing from this page on “xlookup return multiple values”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

Common mistakes

• Selecting ranges of different heights for lookup_array and return_array, which throws a #VALUE! error.
• Assuming approximate match like VLOOKUP — XLOOKUP defaults to exact, so set match_mode to -1 or 1 only when you need the nearest value.
• Hard-coding the lookup value instead of pointing at a cell, so the formula cannot be filled down.

Frequently asked questions