XLOOKUP Formula in Excel With Example

XLOOKUP's flexibility is easiest to appreciate through an example where the columns aren't laid out in the order VLOOKUP would require. This page uses a sample dataset where the value you want returned sits to the left of the lookup column, a layout that would need a workaround in VLOOKUP but works directly in XLOOKUP.

After the worked example, the final section shows Turbo Excel Lookup performing the same match, since its column selection already works independently of layout order.

Here, SKU is the column you're searching by, but it sits to the right of the Price column you want returned - a layout VLOOKUP cannot handle without rearranging the sheet.

Placed in a cell where A2 contains "SKU-91", this formula searches the SKU column (C) for that value and returns the corresponding value from the Price column (A), even though Price sits to the left of SKU in the sheet. The result is 2599. If A2 instead contained a SKU that doesn't exist in the Products table, the formula would return the text "SKU not found" instead of a #N/A error, thanks to the if_not_found argument.

Turbo Excel Lookup's Lookup & Merge tab never cares about column order in the first place - you choose the SKU columns to match on and separately tick Price as the column to pull back, regardless of which side of the sheet either one sits on. This mirrors exactly what makes XLOOKUP more flexible than VLOOKUP, but without needing to reference lookup_array and return_array as two separate ranges.

For a Fill Value equivalent to XLOOKUP's if_not_found, the app has a dedicated Fill Value field on the same tab, applied to every unmatched row across the whole lookup rather than being written per formula.

XLOOKUP's if_not_found argument can also reference another XLOOKUP, creating a fallback chain that tries a second lookup table if the first one doesn't have a match. For the SKU-to-Price example, if a small number of SKUs live in a separate clearance-pricing sheet, a formula could try the main Products table first and fall back to Clearance second:

This kind of chained fallback is possible with nested IFERROR(VLOOKUP(...), VLOOKUP(...)) formulas too, but XLOOKUP's if_not_found makes the intent noticeably clearer to read later. Turbo Excel Lookup handles a similar two-source scenario by running two separate lookups and using the Unmatched export from the first as the Base File input for the second, chaining the same fallback logic without formula nesting.

The Price-to-the-left-of-SKU layout used in this example isn't an unusual edge case - it comes up constantly in real exports, where the column someone else designed a report around rarely matches the order you'd want for a lookup. Being able to reference lookup_array and return_array independently, rather than needing to physically rearrange columns first, saves a genuinely common preparation step that VLOOKUP would otherwise require.

This example works because lookup_array (column C) and return_array (column A) span the same number of rows in the Products table. If a future edit accidentally extends one column further than the other - for instance, pasting extra SKU values into column C without corresponding Price values in column A - XLOOKUP will return a #VALUE! error rather than a partial result, which is a useful safeguard but worth knowing about in advance.

This example highlights exactly what makes XLOOKUP more flexible than VLOOKUP in practice - the freedom to reference lookup and return columns independently, regardless of which side of the sheet either one sits on. That flexibility alone resolves a surprising number of layout headaches.

It's worth remembering that this kind of independent-range lookup is exactly the situation where people most often reach for XLOOKUP over VLOOKUP in the first place, since rearranging source columns just to satisfy VLOOKUP's leftmost-column rule is rarely something you want to do to a file someone else built and maintains.