XLOOKUP Alternative: What I Use Now

Key insights

• Video summary: the creator stopped relying on XLOOKUP and VLOOKUP for many tasks not because those functions are removed, but because other methods scale better and reduce repeated formulas.
  
• Use FILTER when you need every matching result instead of a single hit; it accepts multiple criteria with AND/OR logic and, paired with CHOOSECOLS, returns only the columns you need.
  
• Power Query works best for large or complex datasets: merge tables on a shared key (like Customer ID), import many columns at once, use anti-joins to find unmatched records, and apply fuzzy matching for similar but not identical names.
  
• Power Pivot and the Data Model let you create table relationships and use RELATED, PivotTables, or DAX to retrieve values instead of writing row-by-row lookup formulas.
  
• Formula alternatives: use INDEX+MATCH for compatibility with older Excel versions and INDEX+XMATCH for more modern, array-aware lookups in Microsoft 365.
  
• Practical guidance: keep XLOOKUP for simple, single-value lookups because it searches left/right and supports an if_not_found option; choose FILTER, Power Query, or the Data Model when you need multiple results, better performance, or relational workflows.
  

Video Overview

Microsoft 365

Kenji Farré, known online as Kenji Explains and identified here as an MVP, published a you_tube_video titled "I Stopped Using XLOOKUP. Here's What I Use Instead." The piece frames a shift away from repeated lookup formulas toward methods that scale better for larger or relational workbooks. In the video, Kenji demonstrates two main approaches and walks viewers through concrete examples and timestamps for each technique. Overall, he positions the change as a workflow decision rather than a claim that XLOOKUP is obsolete.

Methods Demonstrated

First, Kenji shows how the FILTER function can replace single-result lookups when you need every match at once, and he pairs it with CHOOSECOLS to control output columns precisely. Then, he presents a second method that uses Power Query to merge tables, identify unmatched rows, and perform fuzzy matching when values differ slightly. He also touches on classic and modern formula alternatives such as INDEX + MATCH and INDEX + XMATCH for compatibility and advanced array-style retrievals. Finally, the video includes short demos for reverse and approximate matches, helping viewers compare options side by side.

Why Alternatives Matter

Kenji explains that lookup functions like XLOOKUP and VLOOKUP (in Excel) normally return the first matching result, which limits use cases where multiple results matter. Consequently, using FILTER makes sense when you need to return several rows at once or apply multiple criteria with AND/OR logic. Moreover, when datasets grow or become relational, repeatedly writing lookup formulas can create maintenance challenges and slow performance. Therefore, moving to query-based or model-based approaches often improves manageability and reduces formula clutter.

Power Query and Data Model Advantages

In the video, Kenji emphasizes that Power Query excels for larger or more complex datasets because it transforms data before it reaches the worksheet. By merging tables on a shared key such as Customer ID, you can bring many columns into one step and use anti joins to identify unmatched records without writing dozens of formulas. Additionally, when data is relational, using the Data Model and tools like RELATED in Power Pivot or DAX summarizes information without repeated row-by-row lookups. These approaches integrate with Power BI and Power Apps, enabling richer reporting and app-driven scenarios. However, the approach requires a change in mindset from formula-first spreadsheets to a modeling workflow that some users must learn.

Tradeoffs and Practical Challenges

Choosing between formulas and tools involves tradeoffs between simplicity, performance, and compatibility. For instance, XLOOKUP remains simple and effective for everyday spreadsheets and has useful features like left-right lookup and an if_not_found option, so it still fits many workflows. On the other hand, Power Query and the Data Model offer scalability and clearer data lineage, but they introduce a steeper learning curve and can complicate collaboration when team members lack experience. Consequently, teams must balance short-term convenience against long-term maintainability when deciding which method to adopt.

Fuzzy Matching and Edge Cases

Kenji also demonstrates fuzzy matching as a practical solution for names or values that are similar but not identical, and he highlights how Power Query supports that capability. Nevertheless, fuzzy matching introduces its own tradeoffs because it can return false positives or require tuning to hit the right balance between recall and precision. Therefore, when accuracy matters, users should validate fuzzy results and consider fallback logic or manual review processes. Ultimately, fuzzy matching is powerful, yet it demands careful implementation to avoid data quality issues.

Recommendations for Users

For readers and spreadsheet owners, Kenji's message is pragmatic: use the right tool for the job rather than defaulting to a single function. If you need backward compatibility or a quick fix, INDEX + MATCH or XLOOKUP remain practical choices; meanwhile, FILTER fits scenarios requiring multiple results or complex criteria. For larger datasets, automated refreshes via Power Automate or relational models, invest time in Power Query and the Data Model to reduce worksheet complexity and improve performance. Finally, test new approaches on copies of your workbooks so you can compare results and confirm that the chosen method meets both accuracy and maintainability goals.

Keywords

XLOOKUP alternatives, replace XLOOKUP with INDEX MATCH, INDEX MATCH tutorial, Excel lookup functions comparison, Power Query merge tables, VLOOKUP alternative, FILTER function vs XLOOKUP, dynamic array lookup Excel