[Excel] Simple Data Lookup Using XLOOKUP and Tables

Introduction

For a long time, VLOOKUP has been a staple Excel function for data lookup and reference. However, its limitations—such as requiring the lookup column to be the leftmost column and necessitating the use of column indices—can be cumbersome. XLOOKUP, introduced in Excel 365 and later versions, addresses these issues by offering a more flexible lookup mechanism. In this article, we’ll explore the basics of XLOOKUP, as well as show how to pair it with Excel’s Table feature to create references based on descriptive column names.

The Basics of XLOOKUP

The XLOOKUP function follows this structure:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Its key advantages include:

• Flexible Lookup Direction: The lookup value can reside anywhere in the range—searching both to the right and to the left.
• Error Handling: You can specify a default value to return when no match is found.
• Concise Syntax:
  You can specify a match_mode and search_mode to choose among exact matches, approximate matches, or wildcard lookups, among others.

  For instance, match_mode settings include:

  • 0 = Exact match
  • -1 = Exact match or next smaller item
  • 1 = Exact match or next larger item
  • 2 = Wildcard match

  Meanwhile, search_mode may be:

  • 1 = Search from the first element
  • -1 = Search from the last element
  • 2 = Binary search (sorted ascending)
  • -2 = Binary search (sorted descending)

Thus, XLOOKUP does away with VLOOKUP’s constraint of having to place the lookup column on the far left, providing much more flexibility when referencing data.

Why Use Tables and Column Names?

Excel’s Table feature offers several benefits:

• Auto-Expansion: If you add or remove rows, the table range updates automatically.
• Improved Readability: Instead of referencing cell ranges, you can refer to columns by name—making your formulas clearer and more maintainable.

For example, consider a table named ProductTable:

Creating and Naming a Table

1. Create the table: Highlight your data range, then go to Insert → Table. Alternatively, press Ctrl + T. If your first row contains headers, check “My table has headers.”
2. Name your table: Click anywhere in the table, go to the Table Design (or Design) tab, and enter a name (e.g., ProductTable) in the Table Name field. This allows you to leverage structured references that include column names.

Once you have a table named “ProductTable,” retrieving the product name for product code A002 with XLOOKUP is as simple as:

=XLOOKUP("A002", ProductTable[Product Code], ProductTable[Product Name], "Not found")

Here, references like ProductTable[Product Code] and ProductTable[Product Name] make it immediately clear which columns are being used. Unlike traditional cell-based references (e.g., A2:A10, B2:B10), these references automatically adjust to changes such as adding new rows or reordering columns—significantly improving maintainability.

Practical Example: Cross-Referencing Multiple Tables

You can take this even further by linking multiple tables. For instance, assume you have two tables: ProductTable and SalesTable.

ProductTable

SalesTable

To add the product name to each row in the SalesTable based on the product code, simply use:

=XLOOKUP([@Product Code], ProductTable[Product Code], ProductTable[Product Name], "Not found")

• [@Product Code] is a structured reference pointing to the current row’s Product Code in SalesTable.
• Because you’ve defined tables for both data sets, each row in SalesTable automatically updates, and future changes in ProductTable are handled seamlessly.

Conclusion

• XLOOKUP eliminates many of VLOOKUP’s limitations, allowing flexible lookup directions and robust error-handling.
• Using Tables in tandem with XLOOKUP enables intuitive, column-based references and automatic range adjustments.
• Combine these features to build dynamic, maintainable spreadsheets that streamline data lookups and minimize errors.

If you’re using Excel and looking to simplify your data references, try pairing XLOOKUP with Tables. It’s an excellent way to maintain clear and adaptable workbooks.