What XLOOKUP does, and using it without Excel 365

XLOOKUP searches one range for a value and returns the matching item from another range, so you can find a price from a product code or a name from a staff number in one tidy formula. You don't need a Microsoft 365 subscription to use it: Grid by Zaweba has XLOOKUP and its partner XMATCH built in, as free software that works offline.

The idea behind XLOOKUP

Older lookups ask you to point at a whole table and say which column number to return. XLOOKUP separates the two jobs. You give it the value to find, the range to look in, and the range to bring back from. Because the return range is its own argument, the key column can sit anywhere in the table, even to the right of the answer, and inserting a new column won't make the formula fetch the wrong field.

It also has its own argument for what to show when nothing matches, so you don't have to wrap it in IFERROR to avoid a stark #N/A. And it can match exactly or find the nearest value, which suits banded tables like postage weights or tax brackets.

A simple example

Say a sheet lists product codes in column A and prices in column C. On an order form, a cell holds the code a customer wants. An XLOOKUP that takes that code, searches column A and returns from column C gives you the price. Change the code and the price changes with it.

Point the return argument at several columns instead of one, and XLOOKUP returns the whole matching row. In Grid that result spills across the neighbouring cells, shown by a blue spill outline, so one formula fills in a product's name, size and price together.

Where XMATCH comes in

XMATCH returns a position rather than a value: the row or column number where a match sits. On its own that's useful for checking whether something is in a list. With INDEX it builds a two-way lookup, finding the row with one XMATCH and the column with another, then picking the cell where they cross. A mileage chart or a rate card by size and colour works well this way.

Using XLOOKUP in Grid

Type =XLOOKUP and Grid's autocomplete offers the function, then shows a tip with the current argument in bold as you go. Clicking cells or using the arrow keys puts references into the formula, and coloured boxes on the grid show which ranges you've used. If you'd rather fill it in step by step, Insert Function (fx) lists it in the Lookup and Reference family with a description of each argument and a live result.

Turning the lookup range into a table helps. Structured references such as Table1[Code] grow as you add rows, so a new product at the bottom of the list is found straight away without editing the formula.

Sharing files that use it

Grid saves its own .zagr files, which are .xlsx inside, and it saves .xlsx directly. A workbook using XLOOKUP opens in other modern spreadsheet programs that support the function. If you're sending a file to someone on a very old version of Excel that predates XLOOKUP, they'll see an error where the lookup should be, so for those cases INDEX with MATCH is the safer choice, and Grid supports that too.

XLOOKUP is the lookup most people reach for once they've tried it, and in Grid by Zaweba it's simply there, alongside the rest of the 37 functions in the Lookup and Reference family, on your own PC.