I personally don’t know why it took this long for Microsoft Excel team to create XLOOKUP function. The fact that VLOOKUP is considered to be one of Excel’s most widely used functions reflects a strong demand in string look up tabulations. Surely, a multitude of VLOOKUP‘s limitations can be overcome with patience, helper columns, INDEX/MATCH, CHOOSE, OFFSET and other constructs. Yet, why would we use any workarounds, when we would rather utilize a more powerful function with multitude of applications? Meet, much anticipated XLOOKUP function, which was officially released to Office 365 subscribers in early February of this year. It offers a really long list of additional benefits; in today’s tutorial we will review 11 scenarios that take full advantage of the following XLOOKUP features:
- LEFT lookup
- Horizontal lookup
- Multi-cell/array retrieval
- Match based on wildcard conditions
- Combination of Vertical AND Horizontal lookups
- Lookup based on multiple criteria
- Lookup in reverse order
- Lookup for maximum/minimum values
- Built-in Error Handling
- Exact match by default
- Flexible approximate match