Excel: How to Use XLOOKUP for IT Audits

Why XLOOKUP Beats VLOOKUP for Asset Audits

Running an IT audit usually means cross-referencing several spreadsheets — asset tags against purchase records, serial numbers against warranty status, user IDs against department assignments. VLOOKUP can technically do this, but it comes with three real limitations: it can only search left-to-right, it breaks silently if a column gets inserted, and it requires an ugly, error-prone column index number. XLOOKUP, available in Excel for Microsoft 365 and Excel 2021 onward, fixes all three.

Basic Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Only the first three arguments are required. Compare that to VLOOKUP's four arguments, where the third — a raw column number you have to count manually — breaks the moment someone inserts or deletes a column in the source table.

A Basic Asset Lookup

Say column A on a sheet named Assets holds asset tags, and column D holds the assigned user's name. On your audit sheet, to find the assigned user for asset tag AST-0472 in cell A2:

=XLOOKUP(A2, Assets!A:A, Assets!D:D)

This reads as: find A2's value inside column A of the Assets sheet, and return the corresponding value from column D — regardless of whether D sits to the left or right of A. VLOOKUP could never search to the left of its lookup column at all; XLOOKUP does not care about direction.

Handling Missing Matches Gracefully

An audit spreadsheet is guaranteed to have some values that do not match — a decommissioned asset, a typo in a serial number, a user who left the company. Without handling this, a missing match returns Excel's ugly #N/A error, which then breaks any formula downstream that references the cell. The fourth argument handles this directly:

=XLOOKUP(A2, Assets!A:A, Assets!D:D, "NOT FOUND")

Now any unmatched row displays the literal text "NOT FOUND" instead of an error — readable at a glance, and safe to reference in a COUNTIF or conditional formatting rule elsewhere in the audit.

Pulling Multiple Columns in One Formula

A genuinely useful XLOOKUP feature VLOOKUP cannot replicate: the return array does not have to be a single column. Reference a full range, and XLOOKUP spills the result across multiple adjacent cells automatically:

=XLOOKUP(A2, Assets!A:A, Assets!D:F)

If columns D through F hold assigned user, department, and purchase date, this single formula spills all three values into the current row without three separate lookups. This requires a version of Excel that supports dynamic array spilling (Microsoft 365, or Excel 2021+).

Bidirectional Lookups With Two Criteria

Audits frequently need to match on more than one field at once — for example, finding a specific serial number within a specific site location, in case the same serial format is reused across offices. XLOOKUP handles this by concatenating criteria directly inside the array arguments:

=XLOOKUP(A2&B2, Assets!A:A&Assets!B:B, Assets!D:D)

Here, A2 holds the serial number and B2 holds the site code; the formula matches rows where both the serial and site concatenation are identical. Note this must be entered as an array formula in older 365 builds — press Ctrl+Shift+Enter if it does not spill correctly on Enter alone (recent 365 builds handle this automatically).

Approximate Matches for Version or Date Ranges

The fifth argument, match_mode, controls how XLOOKUP handles values that are not an exact match — useful for auditing against tiered thresholds like warranty expiration windows or software version brackets:

• 0 (default) — exact match only
• -1 — exact match, or the next smaller item if no exact match exists
• 1 — exact match, or the next larger item if no exact match exists
• 2 — wildcard match, supporting * and ? in the lookup value

=XLOOKUP(A2, Warranty!A:A, Warranty!B:B, "No warranty tier found", -1)

Replacing an Existing VLOOKUP Formula

If you inherited an audit workbook full of VLOOKUP formulas, you do not need to rebuild them from scratch. The translation is direct:

Old: =VLOOKUP(A2, Assets!A:D, 4, FALSE)
New: =XLOOKUP(A2, Assets!A:A, Assets!D:D)

The new version is both more readable — it references the actual return column instead of counting "4 columns over" — and immune to breaking if someone inserts a column into the middle of the Assets!A:D range later.

A Note on Compatibility

XLOOKUP is not available in Excel 2019, Excel 2016, or older perpetual-license versions — only Microsoft 365 and Excel 2021 onward. If your organization shares audit workbooks with people on older Excel versions, opening a file containing XLOOKUP formulas on an unsupported version shows a #NAME? error instead of the result. Confirm your audience's Excel version before standardizing an audit template on XLOOKUP, or keep a VLOOKUP-based fallback version for compatibility.