Syntax in one line
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Unlike VLOOKUP there is no column number, and the default is an exact match, which removes the most common VLOOKUP mistake (forgetting FALSE).
=XLOOKUP(A2, Products[SKU], Products[Price], "Not found")1. Return several columns at once
Pass a multi-column return range and the result spills horizontally. One formula retrieves name, category and price for a SKU.
=XLOOKUP(A2, Products[SKU], Products[[Name]:[Price]])2. Tiered pricing with approximate match
Match mode -1 returns the next smaller item, perfect for commission or shipping tiers. The table no longer needs to be sorted as it did with VLOOKUP TRUE.
=XLOOKUP(B2, Tiers[MinAmount], Tiers[Rate], , -1)3. Find the last occurrence
Search mode -1 scans from bottom to top. Use it to get the most recent status of a ticket or the last price paid by a customer.
=XLOOKUP(A2, Log[TicketID], Log[Status], , 0, -1)4. Two-way lookup
Nest one XLOOKUP inside another to cross a row label and a column header, replacing the classic INDEX/MATCH/MATCH combination.
=XLOOKUP(G2, Sales[Region], XLOOKUP(H2, Sales[#Headers], Sales))5–7. Wildcards, multiple criteria, Google Sheets
Match mode 2 enables wildcards ("*north*"). For multiple criteria, look up 1 in a product of conditions: =XLOOKUP(1, (A:A=G2)*(B:B=H2), C:C). XLOOKUP also exists in Google Sheets with the same arguments. If colleagues still use Excel 2019, keep an INDEX/MATCH version, because XLOOKUP returns #NAME? there.