3. Lookup and reference formulas for equipment records

Equipment records rarely live in a single tidy column. A typical maintenance workbook holds an asset register on one sheet (tag number, description, manufacturer, install date, location, criticality) and a separate log of work orders, calibration events, or inspection readings on another. The analytical task is almost always the same: given a key — a tag number, a serial number, or a combination of area and equipment type — pull the correct attribute or the correct set of rows from the register. This chapter covers the three formula families that handle that retrieval reliably: XLOOKUP for single-key exact retrieval, INDEX/MATCH for the same job in workbooks that must remain compatible with older Excel versions, and FILTER for returning every row that satisfies one or more conditions.

Why VLOOKUP breaks down on engineering registers

The classic VLOOKUP assumes the lookup key sits in the leftmost column of the range and that the returned column is to its right. Engineering registers are frequently reorganized — a new column is inserted for "P&ID reference," or the tag column is moved after the description — and every VLOOKUP silently returns wrong data or #REF!. It also requires a hard-coded column index, so inserting a column shifts every result by one. Both XLOOKUP and INDEX/MATCH remove these constraints by separating the lookup vector from the return vector.

XLOOKUP: the default for single-key retrieval

The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). For an asset register where tag numbers are in Assets!A2:A500 and the manufacturer is in Assets!D2:D500, retrieving the manufacturer for tag P-101A is:

=XLOOKUP("P-101A", Assets!A2:A500, Assets!D2:D500, "Not in register")

Three behaviors matter in practice. First, the fourth argument supplies a controlled fallback instead of #N/A, which keeps downstream calculations clean. Second, the default match mode is exact match (0), which is what you want for tag numbers and serial numbers — approximate matching is only appropriate for banded tables such as pipe-size schedules. Third, XLOOKUP can return an entire row by pointing return_array at multiple columns, which spills the result horizontally:

=XLOOKUP("P-101A", Assets!A2:A500, Assets!B2:F500, "Not in register")

A common engineering pitfall is trailing spaces or non-breaking spaces imported from a CMMS export. XLOOKUP treats "P-101A" and "P-101A " as different keys. Wrap the lookup value in TRIM() and, if the source data is dirty, apply TRIM to the lookup array as well — though that forces an array calculation, so cleaning the register once is preferable.

INDEX/MATCH: the compatible equivalent

Where the workbook must open in Excel 2016 or earlier, or where a corporate template forbids newer functions, INDEX/MATCH delivers the same separation of concerns. MATCH finds the position of the key within the lookup column; INDEX returns the value at that position in the return column:

=INDEX(Assets!D2:D500, MATCH("P-101A", Assets!A2:A500, 0))

The third argument of MATCH must be 0 for exact match. Omitting it defaults to approximate match on an ascending-sorted list, which on an unsorted tag register returns arbitrary rows — a failure mode that produces plausible-looking but wrong data and is therefore more dangerous than an outright error. For two-dimensional retrieval, such as finding the calibration interval for a given tag and a given calibration type, nest two MATCH calls inside INDEX:

=INDEX(Cal!C2:Z200,
       MATCH("P-101A", Cal!A2:A200, 0),
       MATCH("Pressure", Cal!C1:Z1, 0))

Here the row is located by tag and the column by calibration type, so the formula survives insertion of new rows or columns anywhere in the table.

FILTER: multi-condition retrieval of whole records

Single-value lookups answer "what is the manufacturer of P-101A?" Multi-condition questions are different: "which instruments in Area 200 are overdue for calibration?" or "list every work order on P-101A closed after 1 March." FILTER returns all matching rows as a spilled array:

=FILTER(Assets!A2:F500,
        (Assets!E2:E500="Area 200") * (Assets!F2:F500="Overdue"),
        "No matches")

The include argument combines conditions with multiplication, which acts as logical AND; addition acts as OR. Each condition must be wrapped in parentheses so the comparison binds before the arithmetic. The result spills into as many rows as there are matches and as many columns as the source range — so leave empty space below and to the right, or the formula returns #SPILL!. If the spill is blocked by existing content, Excel highlights the obstructing cells.

For a bounded, report-friendly output, wrap FILTER in SORT and TAKE to return only the top N overdue items by criticality:

=TAKE(SORT(FILTER(Assets!A2:F500,
                   (Assets!E2:E500="Area 200") * (Assets!F2:F500="Overdue"),
                   "No matches"),
            6, -1),
      10)

This returns the ten most critical overdue instruments in Area 200, sorted descending on column 6. The "No matches" fallback is essential: without it, an empty result propagates #CALC! through any dependent formula.

Choosing between the three

The decision is driven by the shape of the answer, not by preference. If the answer is one value per key, use XLOOKUP (or INDEX/MATCH for compatibility). If the answer is a set of rows, use FILTER. If the answer is a value at the intersection of a row key and a column key, use two-dimensional INDEX/MATCH. The diagram below maps these retrieval shapes to the appropriate formula and shows the key inputs each one consumes.

Worked example: overdue calibration report

Assume an asset register on sheet Assets with columns A–F: Tag, Description, Manufacturer, Install Date, Area, Status. A calibration log on sheet Cal holds Tag, Last Cal Date, Interval Months, and Result. The task is to produce a report of every instrument in Area 200 whose status is "Overdue," showing tag, description, and last calibration date, sorted by tag.

First, confirm the register has no duplicate tags — a duplicate key makes XLOOKUP return the first match silently, which is a data-quality problem, not a formula problem. Use =COUNTIF(Assets!A2:A500, Assets!A2) down the column and flag any value greater than 1. Next, build the report formula on a clean sheet:

=SORT(FILTER(Assets!A2:C500,
              (Assets!E2:E500="Area 200") * (Assets!F2:F500="Overdue"),
              "No overdue instruments in Area 200"),
      1, 1)

Expected output is a spilled block with one row per matching instrument, sorted ascending by tag. Verify the count against a control total: =COUNTIFS(Assets!E2:E500,"Area 200",Assets!F2:F500,"Overdue") should equal the number of spilled rows. If the spill returns #SPILL!, clear the cells below and to the right. If it returns the fallback string when matches are known to exist, check for trailing spaces in the Area and Status columns — the most frequent cause of a false empty result.

To attach the last calibration date from the log, wrap the lookup inside the filter output using XLOOKUP with a fallback of "Never":

=XLOOKUP(A2#, Cal!A2:A2000, Cal!B2:B2000, "Never")

The A2# spill reference applies the lookup to every tag in the filtered block at once, producing a parallel column of dates. This combination — FILTER to select the record set, XLOOKUP to enrich each row — is the standard pattern for engineering reporting workbooks and scales cleanly as the register grows.