Advanced XLOOKUP
Make duplicate handling, multiple conditions and threshold rules explicit.
By the end, you’ll be able to:
- Search backward when the final matching record is intended
- Combine two conditions in a lookup mask
- Choose exact or next-smaller matching for a business rule
A match needs a business meaning
One SKU can have different regional prices and several historical revisions. An ordinary first-match lookup can return a valid price for the wrong region or revision. Decide what identifies a record before selecting a formula.
In the table below, P101/West has prices 20 and 22. The rows are arranged oldest to newest. A reverse search can find the last matching record, but it does not inspect dates or prove that a later row is chronologically newer.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Region | Price | Find SKU | Find region | |
| 2 | P101 | East | 18 | P101 | West | |
| 3 | P101 | West | 20 | |||
| 4 | P102 | East | 25 | |||
| 5 | P101 | West | 22 |
Read the optional arguments
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])lookup_value is what to find; lookup_array is the search vector; return_array contains aligned results. if_not_found replaces a missing-match #N/A. match_mode is 0 for exact, -1 for exact or next smaller, 1 for exact or next larger, and 2 for wildcard matching. search_mode is 1 for first-to-last, -1 for last-to-first, or 2/-2 for binary search on correctly sorted ascending/descending data.
Binary search can give incorrect results on wrongly sorted data. The exercises use ordinary forward/reverse searches; wildcard, binary and newer release-specific modes are outside this checker.
Worked example: two conditions
=XLOOKUP(1,(A2:A5=E2)*(B2:B5=F2),C2:C5,"Missing") searches for a row where both SKU and Region match. Each comparison produces a true/false column. Multiplication treats TRUE as 1 and FALSE as 0, so only two true conditions produce 1.
For P101/West the mask is 0, 1, 0, 1. The default search returns 20. Add exact mode and reverse search: =XLOOKUP(1,(A2:A5=E2)*(B2:B5=F2),C2:C5,"Missing",0,-1). This returns 22 from the last matching record.
This finds one match. To retrieve all matching records, use FILTER in the next lessons. A lookup does not add duplicate records together.
Worked example: a threshold schedule
A delivery schedule stores minimum quantities 0, 50 and 100 with charges 8, 4 and 0. For quantity 75, exact-or-next-smaller matching chooses the 50 threshold and returns 4: =XLOOKUP(E2,A2:A4,B2:B4,"Below schedule",-1).
For 50 the exact threshold applies; for 100 the charge is 0. Test boundary cases and quantities below the lowest threshold. Keep thresholds unique, numeric and in a readable order. The ordinary linear next-smaller search selects a numeric nearest threshold; it is different from guessing an approximate text identifier.
Check compatibility and data quality
Excel versions: Dynamic arrays, FILTER, SORT, UNIQUE, XLOOKUP and LET are available in Microsoft 365, Excel 2021 and Excel 2024, including supported Mac versions. LAMBDA requires Microsoft 365 or Excel 2024. The browser exercises work without installing Excel; they implement the lesson's stated subset rather than the complete Excel calculation engine.
XLOOKUP is not available in Excel 2016 or Excel 2019. Use an appropriate older lookup pattern when those recipients need to calculate the file. Keep lookup and return ranges the same height and ensure the key fields have consistent types.
A Missing label is useful for an unmatched key; it does not validate the source price or suppress every possible error. Review duplicate counts and the row-order rule before relying on a last-record lookup.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
Choose the key, duplicate rule and matching mode from the business question. Reverse search means last in the range, not automatically latest by date.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.