Bottom Line: While XLOOKUP is a powerful function, it has limitations. In this post, we’ll explore five alternative techniques that can enhance your lookup capabilities in Excel.
Skill Level: Intermediate
Watch the Tutorial
Watch on YouTube & Subscribe to our Channel
Download the Example File
Use this file to follow along with the examples in the video.
Why Look Beyond XLOOKUP?
XLOOKUP is great for simple, single-match lookups, but there are cases where you need more flexibility—like returning multiple matches, handling missing values, or performing lookups across multiple datasets. That’s where these five powerful alternatives come in.
1. XMATCH – Find the Row Number
Unlike XLOOKUP, which returns the lookup value, XMATCH returns the position of that value in a range. This is useful when you need to dynamically reference row numbers in other formulas.
How It Works:
- Use
XMATCHto locate the row position of a value. This returns the row number of the dataset, not necessarily the sheet row number. - To find the exact row number in the worksheet, combine XMATCH with the ROW function.
When to Use: Quickly locate where a value appears in a dataset for further analysis.
Bonus: You can wrap XLOOKUP in the ROW function to return the row number of the sheet for the matching value. This works because XLOOKUP actually returns a range instead of a value.
2. COUNTIF – Count Matches
XLOOKUP stops at the first match, but what if you need to count how many times a value appears? COUNTIF is the perfect tool for this.
How It Works:
COUNTIFscans a range and counts the occurrences of a given value.- If no match is found, it returns 0, making it easy to check for missing data.
When to Use: Find duplicate entries or quickly verify if a value exists multiple times in a dataset.
Pro Tip: If COUNTIF returns a zero, then the value does not exist in the lookup range.
3. FILTER – Return Multiple Matches
Unlike XLOOKUP, which returns just one result, FILTER can return all matching values dynamically.
How It Works:
FILTERextracts all rows where a condition is met.- It creates a spill range, displaying multiple matches automatically.
Tip: If working inside a table, use
ARRAYTOTEXTto display the results in a single cell.
When to Use: When you want to retrieve all employees assigned to a project or all orders from a single customer, for example.
4. Pivot Tables – Compare Multiple Lists
For quick comparisons—such as checking which customers appear in multiple datasets—a Pivot Table is the best tool.
How It Works:
If we have two datasets, such as a list of volunteers from last year and a list from this year, and we want to know which volunteers are found in both lists:
- Stack your datasets into a single table, making sure there is a column that identifies the year.
- Create a Pivot Table with Year in the Columns area and Emails in the Rows area as well as the Values area.
- The count values indicate how many times the person appears in the list, so you can easily spot multiples.
When to Use: Instantly identify returning customers or employees who worked in multiple years.
Pro Tip: Checkout my post on How to Compare Two Sheets in Excel: Bank Reconciliations Made Easy for more details on this technique.
5. Power Query – Automate Lookups Across Large Datasets
For large datasets or recurring lookups, Power Query offers a no-formula approach.
How It Works:
- Use Merge Queries in Power Query to combine tables based on a common column.
- Select only the fields you need and output a clean dataset.
Tip: If you're unfamiliar with Power Query, check out our Power Query Pro Course to master data automation.
When to Use: Best for regular data imports where lookup values change frequently.
Which Alternative Should You Use?
| Scenario | Best Alternative |
|---|---|
| Find the row number of a match | XMATCH |
| Count occurrences of a value | COUNTIF |
| Return multiple matching results | FILTER |
| Compare lists and identify multiples | Pivot Tables |
| Automate lookups in large datasets | Power Query |
Each of these methods extends Excel’s lookup capabilities beyond what XLOOKUP alone can do.
Conclusion
Mastering these alternatives will level up your Excel skills, making your lookups more powerful and flexible.
Which method will you try first? Leave a comment below and let us know!
PS – I mentioned in the video that I have a helpful post on Excel Tables. You can access that here: Excel Tables Tutorial Video
very helpful, thank you