XLOOKUP in Excel, Step by Step

This page builds an XLOOKUP formula up one step at a time, using a small invoice table as the running example. Rather than presenting the finished formula first, each step below adds one argument, so it's clear what each piece is doing and why it's positioned where it is.

By the final step, the same process applies to any similarly structured lookup table, whether it holds invoices, customer records, or inventory - the step-by-step logic stays identical even as the underlying data changes.

Using this invoice table, the goal is to find the Client for INV-3303:

The lookup_value is "INV-3303", found in the Invoice No column.

Unlike VLOOKUP's combined table_array, XLOOKUP's lookup_array points only at the Invoice No column itself: A2:A4.

return_array points directly at the Client column: B2:B4. There's no counting involved - you select the exact column you want back.

For matching many invoice numbers at once instead of typing this formula repeatedly, Turbo Excel VLookup runs the same lookup across an entire invoice register in a single action.

Selecting the lookup_array and return_array portions of a formula and pressing F9 shows their calculated values directly in the formula bar, which is a fast way to confirm both ranges are pointing at the expected data before assuming there's a deeper problem.

Editing the same range that lookup_array and return_array point to, especially while rows are being sorted or filtered, is a common source of confusing results. A clearly separated source sheet that's rarely edited directly reduces that risk.

XLOOKUP's optional fifth argument, match_mode, defaults to an exact match, but it also supports approximate matches - either the next smaller or next larger value when no exact match exists - by passing -1 or 1 instead of the default 0.

This is useful for scenarios like matching a score against grade thresholds, where an exact match won't always exist but the closest applicable bracket still needs to be found.