Since its introduction, XLOOKUP has established itself as Excel’s most versatile and sophisticated lookup tool. By addressing the fundamental bottlenecks of its predecessor, VLOOKUP, it has effectively made older methods obsolete. In this guide, we will dive beneath the surface to explore the true power of the XLOOKUP function.
You will learn how to leverage XLOOKUP for:
- Approximate Matches: Handling ranges and tiered data effortlessly.
- Multiple Criteria: Searching based on two or more conditions without complex workarounds.
- Two-Dimensional Lookups: Finding values at the intersection of specific rows and columns.
XLOOKUP Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Approximate Match
By default, XLOOKUP searches for an exact match, which is ideal for unique identifiers like names or IDs. However, when working with financial data or numerical ranges, such as budget comparisons or tax brackets an approximate match is often necessary.
Consider a scenario where you have a list of vendors and their estimated quotes (Sample data below). If your approved budget doesn’t exactly match a vendor’s quote, we can employ XLOOKUP to find the vendor with the closest amount without exceeding the approved budget.
With the approved budget being #295, 000, we will write an XLOOKUP to return the name of the vendor and their price with the closest amount to the approved budget.
The Formula:
=XLOOKUP(H8,F6:F10, E6:F10, ,-1)
How it Works:
To find the most suitable vendor within our budget, we use five specific arguments within the XLOOKUP function:
- Lookup_value (H8): This is our approved budget (₦295,000).
- Lookup_array (F6:F10): The range containing the vendor’s quoted prices.
- Return_array (E6:F10): The range containing both the Vendor Names and Prices. By selecting two columns here, XLOOKUP will automatically “spill” both the name and the price into adjacent cells.
- If_not_found (omitted): We’ve left this blank (using the double comma
, ,), meaning the formula will return a standard error if no match is possible. - Match_mode (-1): This is the key to the approximate match. By choosing
-1, we instruct Excel to find an exact match or, if one doesn’t exist, the next smaller item.
The Result:
Since ₦295,000 does not exist exactly in our list, XLOOKUP identifies the next value down, which is Vendor D at ₦290,000. Because our return array included two columns, Excel populates both the vendor name and the exact price simultaneously.
Pro Tip: If you want to find the vendor with the closest price that is higher than the budget, you would simply change the Match Mode to 1 (Next larger item).
Multiple Criteria
Building on our vendor dataset, we have enriched the information with a Region column. While the condition for our approved budget remains the same, we now need to ensure the vendor is specifically located in the South.
With XLOOKUP, performing a multi-condition search is straightforward. By applying a slight tweak to the formula logic using Boolean multiplication, we can filter for both budget and location simultaneously.
The Formula
=XLOOKUP(1, (G6:G10<=I8)* (F6:F10=J8), CHOOSECOLS(D6:G10,2,4))
How it Works
The secret to multiple criteria in XLOOKUP is searching for the number 1 (True) within an array of results generated by our conditions:
- Lookup_value (1): We are looking for the instance where all our conditions are met (True).
- Lookup_array (Conditions): We multiply our two criteria together:
(G6:G10<=I8): Checks if the Price is less than or equal to the Budget.(F6:F10=J8): Checks if the Region is “South”.- The Logic: In Excel,
TRUE * TRUE = 1. Any other combination (likeTRUE * FALSE) equals0. XLOOKUP searches this resulting array of 1s and 0s for the first 1.
- Return_array (CHOOSECOLS): Instead of selecting a single range, we use
CHOOSECOLS(D6:G10, 2, 4). This tells Excel to look at our entire table and specifically return the 2nd column (Vendor) and the 4th column (Price).
The Result
Based on the criteria, the formula identifies Vendor D. Even though Vendor B is also in the South, their price (₦400,000) exceeds our budget. Vendor D is the only option that satisfies both being in the South and staying under ₦295,000. The “Spill” feature automatically populates both the Vendor name and their Price into the result cells.
Two Dimensional (2D) Lookups
There are times when our search criteria lie at the intersection of rows and columns. This is another area where XLOOKUP shines.
Our Vendor Quote data has been updated to include different service levels, ranging from Basic to Premium. We will now write a nested XLOOKUP to return the specific price for a vendor based on the chosen service type.
The Formula:
=XLOOKUP(G8,B6:B10,XLOOKUP(H8,C5:E5,C6:E10))
How it Works
This “Nested XLOOKUP” approach works like plotting a graph on a Cartesian plane (X and Y axis) and then looking for the intercept.
- The Inner XLOOKUP:
XLOOKUP(H8, C5:E5, C6:E10)This handles the horizontal search (the X-axis). It looks for the Service Type (e.g., “STANDARD”) in the header row (C5:E5). Once found, it returns the entire column of prices associated with that service level. - The Outer XLOOKUP:
XLOOKUP(G8, B6:B10, ...)This handles the vertical search (the Y-axis). It takes the column of prices provided by the inner formula and searches the Vendor list (B6:B10) for the specific vendor (e.g., “Vendor C”).
By nesting these, the inner formula isolates the correct column, and the outer formula narrows it down to the correct row.
The Result
Mastering XLOOKUP is more than just learning a new formula; it’s about future-proofing your workflow. By moving away from the rigid constraints of VLOOKUP and INDEX/MATCH. As you continue to build more complex reports, remember that XLOOKUP is designed to grow with your data. Whether you’re matching budgets or navigating multi-dimensional service tiers, you now have the most powerful tool in the Excel arsenal at your fingertips.
Thank you for staying till the end !!!
