Looking things up with XLOOKUP and INDEX/MATCH in Grid

A lookup is how one table talks to another. A price list feeds an order form, a list of staff codes fills in names on a rota, a table of postage bands gives the right charge for a parcel's weight. Grid by Zaweba has a Lookup and Reference family of 37 functions for this, and a few of them will do almost all the work you ever need.

XLOOKUP first

For new sheets, start with XLOOKUP. You give it the value to find, the column to search, and the column to return from, and it hands back the matching value. Because the search column and the return column are separate arguments, it doesn't matter which side of the table the key sits on, and inserting a column in the middle of the table won't throw the answer off.

XLOOKUP can also return a whole row or column rather than one cell. Point the return argument at several columns and the matching row spills across, so one formula brings back a customer's name, town and phone number together. With dynamic arrays, that spill updates when the source table changes.

Build in a fallback. If the key isn't found, a lookup returns an error, and an error on an order form looks alarming. Use XLOOKUP's own "if not found" argument, or wrap the formula in IFERROR, to show something calmer such as "Not on the list".

INDEX and MATCH

INDEX returns the value at a given position in a range, and MATCH finds the position of a value. Together they make a flexible lookup that has worked in spreadsheets for decades, and you'll find it in many older workbooks. XMATCH is the modern partner to MATCH, with more control over how matches are made.

The pair still earns its place for two-way lookups. Use one MATCH to find the row and another to find the column, then INDEX to pick the cell where they cross. A mileage chart or a table of rates by size and colour is a classic example.

The older style lookups

Grid also has the vertical and horizontal lookups that have been around the longest. They search the first column or row of a table and return a value a set number of columns or rows across. They work, and workbooks that use them open and calculate as you'd expect. Their weakness is that column number: add a column to the table and the formula quietly returns the wrong field. When you're building something new, XLOOKUP avoids that trap.

OFFSET and INDIRECT

OFFSET returns a range a given number of rows and columns away from a starting cell, and can size it too. INDIRECT turns text into a reference, so a cell containing a sheet name can steer a formula to that sheet. Both are powerful for summary sheets and dashboards, but use them sparingly. A formula built from text is harder to follow, and trace precedents can't show you the full picture when the reference is assembled on the fly.

Habits that keep lookups honest

Make the lookup table a proper table. A table grows as you add rows, and structured references in your formulas grow with it, so a new product at the bottom of the price list is found straight away. A named range does a similar job if you'd rather not use a table.

Watch for stray spaces and numbers stored as text. A code typed as "A101 " with a trailing space won't match "A101". TRIM in a helper column, or the AI Clean up data action if you've connected your own AI account, can tidy a column before you rely on it.

Decide whether you want an exact match or the nearest one. Exact is right for codes and names. A nearest match is right for bands, such as tax brackets or postage weights, where the table lists the lower edge of each band and the lookup should find the band a value falls into.

Lookups are the glue in most useful workbooks. In Grid by Zaweba they behave the way spreadsheet users already know, so the patterns above work just as well in a sheet you've been given as one you build yourself.