Expanding XLOOKUP for Real Use Cases
Swipe to show menu
Basic lookups are useful for connecting tables, but real analytical systems often require more advanced retrieval logic. In this chapter, expand XLOOKUP to support multi-column outputs, dynamic ranking workflows, and reverse searches.
Multi-Column XLOOKUP
XLOOKUP can return multiple columns at once if the return array spans across several columns.
Formula used in this chapter:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
E2: lookup value;Products[Product]: lookup column;Products[[Category]:[Price]]: multi-column return array;"Not found": fallback value.
The result spills horizontally across multiple columns.
MAX Structure
=MAX(array)
array: numeric values being evaluated.
Formula used in this chapter:
=MAX(Sales_Data[Revenue])
This returns the highest Revenue value in the dataset.
Reverse Search with XLOOKUP
=XLOOKUP(J2,Sales_Data[Sales_Rep],Sales_Data[Order_ID],"Not found",0,-1)
0: exact match mode;-1: searches from last to first.
This returns the most recent matching record instead of the first one.
Inside the Summary sheet, create a small product lookup section.
Inside F2, type:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
The product details spill horizontally across multiple columns.
Change the product name inside E2.
Notice that all related attributes update automatically.
Inside a separate cell, type:
=MAX(Sales_Data[Revenue])
This identifies the highest transaction value in the dataset.
Inside E2, type:
=XLOOKUP(MAX(Sales_Data[Revenue]),Sales_Data[Revenue],Sales_Data[Product],"Not found")
MAX(Sales_Data[Revenue]): highest Revenue value;Sales_Data[Revenue]: lookup column;Sales_Data[Product]: returned product.
The formula dynamically retrieves the product connected to the highest Revenue transaction.
Inside F2, type:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
The full product profile now updates dynamically together with the top product.
Create a Sales Rep input section. Inside the lookup result cell, type:
=XLOOKUP(J2,Sales_Data[Sales_Rep],Sales_Data[Order_ID],"Not found",0,-1)
The formula now returns the most recent order for the selected sales rep.
Change the Sales Rep value inside J2.
Confirm that:
- The lookup always returns the latest matching order;
- Repeated values no longer return only the first occurrence.
1. What happens when XLOOKUP uses a multi-column return array?
2. Why is MAX combined with XLOOKUP in analytical models?
3. What is the effect of using search_mode = -1 in XLOOKUP?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
