Study guide
9.1 Modern Lookups with XLOOKUP & XMATCH
Microsoft Excel Expert Microsoft 365 Apps
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture
2 Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution
3 Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations
4 Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)
5 Chapter 5: Custom Formatting & Data Validation
5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing
6 Chapter 6: Data Grouping, Summarization, & Cleansing
6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing
7 Chapter 7: Advanced Conditional Formatting & Filtering
7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges
8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions
8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
9 Chapter 9: Advanced Lookup Functions & Variable Optimization
9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET
10 Chapter 10: Date, Time, Text, & Auditing Functions
10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools
11 Chapter 11: Dynamic Array Functions & Spill Operations
11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions
12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic
13 Chapter 13: Macro Automation & VBA Fundamentals
13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor
14 Chapter 14: Advanced & Specialized Visualizations
14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts
15 Chapter 15: PivotTable Construction & Field Configuration
15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences
16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts
16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts
9.1 Modern Lookups with XLOOKUP & XMATCH
Key Takeaways Explain This Topic AI
XLOOKUP fundamentally modernizes data retrieval by decoupling lookup and return arrays, defaulting to exact matching (match_mode 0), and supporting leftward and vertical/horizontal lookups without column index numbers.
The optional [if_not_found] argument provides native error handling, cleanly intercepting missing records without the computational overhead of external IFERROR or IFNA wrappers.
Advanced match modes support exact or next smaller (-1), exact or next larger (1), and wildcards (2), while search modes enable reverse scanning from bottom to top (-1) and high-speed binary searches (2 and -2).
XLOOKUP can return multi-column or multi-row dynamic arrays that spill across adjacent cells, and nesting two XLOOKUP functions enables dynamic two-way matrix lookups across rows and columns.
XMATCH returns the relative 1-based index position of an item within an array or range, superseding legacy MATCH by defaulting to exact matching and supporting reverse and binary search options.
Last updated: September 2026
Modern Lookups with XLOOKUP & XMATCH
Data retrieval is a core operational requirement in enterprise financial modeling, auditing, and business intelligence. For decades, spreadsheet engineers relied on legacy functions such as VLOOKUP , HLOOKUP , and LOOKUP . While functional, these legacy tools suffer from structural fragility: VLOOKUP requires hardcoded column index numbers that break whenever table columns are inserted or deleted, cannot natively retrieve data positioned to the left of the lookup column, and defaults to approximate matching if the final argument is omitted—frequently returning silent errors.
The modern Microsoft 365 calculation engine resolves these architectural limitations through XLOOKUP and XMATCH . Introduced to replace VLOOKUP , HLOOKUP , and legacy MATCH , these functions separate lookup and return vectors, default to exact match behavior, support dynamic array spilling, and offer native error handling.
Anatomy and Argument Architecture of XLOOKUP
The XLOOKUP function searches a range or array for a specified match and returns the corresponding item from a second range or array. Its syntax contains three required arguments and three optional arguments: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Argument Required / Optional Description & Functional Rules
lookup_value Required The value or cell reference to search for. Supports text, numbers, dates, wildcards, and dynamic array references.
lookup_array Required The 1D range or array vector to search across (can be a vertical column or horizontal row).
return_array Required The range or array containing values to return. Can span a single column/row or multiple adjacent columns/rows.
[if_not_found] Optional The value or text returned when no match is found (e.g., "Not Found" , 0 , or "" ). If omitted and no match exists, returns #N/A .
[match_mode] Optional Specifies match type: 0 (Exact, default), -1 (Exact or next smaller), 1 (Exact or next larger), 2 (Wildcard match: * , ? , ~ ).
[search_mode] Optional Specifies search direction and algorithm: 1 (First-to-last, default), -1 (Last-to-first / reverse), 2 (Binary search, ascending), -2 (Binary search, descending).
Because lookup_array and return_array are separate arguments, XLOOKUP can look in any direction. If the lookup values reside in column D and the desired return values reside in column A , the formula =XLOOKUP(G2, D2:D100, A2:A100) executes a left-lookup seamlessly without rearranging the worksheet.
Parameter Deep Dive: Match Modes and Search Modes
Configuring match_mode and search_mode allows XLOOKUP to solve complex enterprise retrieval challenges that previously required convoluted array formulas.
Match Mode Behaviors
0 — Exact Match (Default) : Finds the first item exactly matching lookup_value . If no exact match is found, returns #N/A (or if_not_found ).
-1 — Exact Match or Next Smaller : Matches the exact value, or the largest value that is smaller than lookup_value . This is the standard setting for tiered commission schedules, progressive tax brackets, and volume discount tiers where inputs fall between defined minimum thresholds.
1 — Exact Match or Next Larger : Matches the exact value, or the smallest value that is greater than lookup_value . Ideal for shipping weight bands and inventory reorder triggers where any quantity exceeding a tier bumps into the next tier.
2 — Wildcard Match : Enables the question mark ( ? ) to match any single character, the asterisk ( * ) to match any sequence of characters, and the tilde ( ~ ) to escape literal wildcards.
Search Mode Behaviors
1 — First to Last (Default) : Searches sequentially from the first element of lookup_array to the last.
-1 — Last to First (Reverse Lookup) : Scans backwards from the bottom or end of the array to the top. In chronological transactional ledgers where new entries are appended at the bottom, setting search_mode to -1 retrieves the most recent transaction for a customer or stock ticker without sorting.
2 and -2 — Binary Searches : Executes binary search algorithms on pre-sorted arrays ( 2 requires ascending sort; -2 requires descending sort). While binary search offers O(log n) computational efficiency on massive datasets (100,000+ rows), it returns invalid results if the underlying array is not strictly sorted.
Native Error Handling with [if_not_found]
In legacy formulas, shielding users from #N/A errors required wrapping formulas in error traps: =IFERROR(VLOOKUP(E2, A2:D100, 4, FALSE), "Not Found")
This pattern has two significant drawbacks: it obscures the formula's core intent and suppresses all errors, including internal syntax mistakes, reference corruption ( #REF! ), and division by zero ( #DIV/0! ).
XLOOKUP replaces this pattern with its built-in fourth argument: =XLOOKUP(E2, A2:A100, D2:D100, "Not Found")
This parameter intercepts only lookup misses ( #N/A ), allowing other legitimate calculation errors to surface during auditing while avoiding additional calculation overhead.
Multi-Column Returns & Dynamic Spilling
XLOOKUP is fully integrated into Excel's dynamic array calculation engine. When return_array spans multiple contiguous columns or rows, XLOOKUP returns a dynamic array that automatically spills across adjacent worksheet cells. =XLOOKUP("SKU-104", A2:A100, C2:E100)
If cell G2 contains this formula, Excel populates G2 with Description (column C), H2 with Unit Price (column D), and I2 with Stock Quantity (column E). The formula exists solely in G2 , while H2:I2 display the spilled output surrounded by a thin blue ghost border. Downstream formulas can reference the entire spilled range using the spill operator ( G2# ).
Two-Way (Matrix) Dynamic Lookups
Complex financial schedules often require querying a 2D data grid by matching both a row identifier and a column identifier. Nesting XLOOKUP inside another XLOOKUP constructs an elegant two-way lookup: Row Headers (A2:A5): Department names
- Col Headers (B1:E1): Quarter names (Q1, Q2, Q3, Q4)
- Data Grid (B2:E5): Budget figures
Lookup Target: G2 = "Marketing" (Row) | H2 = "Q3" (Column) =XLOOKUP(G2, A2:A5, XLOOKUP(H2, B1:E1, B2:E5)) Step 1: Inner XLOOKUP searches H2 ("Q3") in B1:E1
- └── Returns the entire Q3 column vector (D2:D5)
- Step 2: Outer XLOOKUP searches G2 ("Marketing") in A2:A5
- └── Uses D2:D5 as its return_array to yield the intersection value
- Relative Position Retrieval with XMATCH
When a model requires the numerical position of an item rather than the value itself, XMATCH replaces the legacy MATCH function: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Unlike legacy MATCH (which defaults to approximate match 1 ), XMATCH defaults to exact match ( 0 ), eliminates the need to specify the match parameter for standard lookups, and supports reverse lookups ( search_mode: -1 ) and wildcards ( match_mode: 2 ).
High-Frequency MO-211 Exam Traps
Dimension Mismatch ( #VALUE! ) : The lookup_array and return_array must share identical lengths along the search axis. Entering =XLOOKUP(F2, A2:A50, B2:B60) produces an immediate #VALUE! error because the lookup vector contains 49 cells while the return vector contains 59 cells.
Unsorted Binary Searches : Setting search_mode to 2 or -2 on an unsorted dataset returns incorrect values without generating an error alert. Never use binary search unless the prompt explicitly confirms the data is sorted.
Spill Collisions ( #SPILL! ) : When returning multi-column ranges, ensure all target spill cells are completely empty. Any existing text, value, or formatting block generates a #SPILL! error.
Approximate Bracket Reversal : In graduated tax or commission tables, using match_mode: 1 (next larger) when brackets define lower bounds will incorrectly assess taxpayers in the higher tax bracket.