Note: This publish-ready article is written in standard American English and uses current, practical Excel information synthesized from reliable Excel education and Microsoft documentation sources.
Excel has always been a little like a very organized librarian: helpful, powerful, and occasionally intimidating when you ask the wrong question. For years, users relied on VLOOKUP, HLOOKUP, INDEX MATCH, helper columns, and a generous amount of caffeine to find information in spreadsheets. Then Microsoft introduced XLOOKUP, and suddenly lookup formulas became cleaner, more flexible, and much less likely to make your worksheet look like it was built during a thunderstorm.
But here is the fun part: the real magic begins when you learn how to use XLOOKUP with multiple criteria in Microsoft Excel. Instead of searching by only one value, you can ask Excel to find a result based on two, three, or even more conditions. For example, you might want to find the price of a product based on its size, color, and region. Or maybe you need to return an employee bonus based on department, job title, and performance rating. One criterion is nice. Multiple criteria are where Excel starts wearing a cape.
In this guide, you will learn how multiple criteria XLOOKUP works, when to use Boolean logic, when to use concatenation, how to avoid common formula mistakes, and how to build practical lookup formulas that are easy to maintain. We will use clear examples, plain English, and just enough spreadsheet humor to keep your formulas from feeling like tax paperwork.
What Is XLOOKUP in Excel?
XLOOKUP is a modern Excel function designed to search for a value in a range or array and return a matching result from another range or array. In simple terms, it looks for something and brings back the related answer. Unlike VLOOKUP, XLOOKUP does not require the lookup column to be on the left side of the return column. It can search vertically, horizontally, from top to bottom, from bottom to top, and it can return custom messages when no match is found.
The basic syntax is:
The first three arguments are required. The optional arguments let you control what happens when no match is found, whether Excel should use exact or approximate matching, and which direction Excel should search. For everyday work, the default exact match behavior is one of XLOOKUP’s biggest advantages. You do not need to type FALSE at the end of the formula like you often did with VLOOKUP. Excel finally decided to be polite.
Why Use XLOOKUP with Multiple Criteria?
A normal XLOOKUP searches for one value. That works beautifully when each lookup value is unique. For example, if every employee has a unique ID, you can search by employee ID and return the employee name, department, salary, or start date.
However, many real-world spreadsheets are not that tidy. You may have repeated product names, repeated regions, repeated dates, or repeated customer names. Searching by only one field can return the wrong result because multiple rows may share the same value. That is where Excel XLOOKUP multiple criteria formulas become extremely useful.
Multiple criteria allow you to narrow the search. Instead of asking Excel, “Find Hoodie,” you can ask, “Find Hoodie where the size is Medium and the color is Blue.” That is much more specific, and specific is exactly what Excel formulas love.
The Best Method: XLOOKUP with Boolean Logic
The most flexible way to use XLOOKUP with multiple criteria is Boolean logic. Boolean logic turns each condition into TRUE or FALSE. In Excel formulas, TRUE behaves like 1 and FALSE behaves like 0. When you multiply several conditions together, only rows where every condition is TRUE will equal 1.
Here is the general structure:
Let’s say you have a sales table with product names in column A, sizes in column B, colors in column C, and prices in column D. You want to find the price for a Medium Blue Hoodie. Your formula could look like this:
Here is what happens behind the curtain:
Excel checks whether each row in A2:A100 equals “Hoodie.” Then it checks whether each row in B2:B100 equals “Medium.” Then it checks whether each row in C2:C100 equals “Blue.” Each test creates an array of TRUE and FALSE values. Multiplying those arrays together creates a final array of 1s and 0s. XLOOKUP searches for 1 and returns the price from column D for the first row where all conditions are true.
That may sound fancy, but the idea is simple: every condition must pass. If one condition fails, the row is not the match. Excel is basically acting like a very picky bouncer at a nightclub called “Correct Data Only.”
Using Cell References Instead of Hard-Coded Values
Hard-coding values directly into formulas is fine for quick tests, but it is not ideal for reusable spreadsheets. A better approach is to place your criteria in cells and refer to those cells in your formula.
Suppose G2 contains the product, G3 contains the size, and G4 contains the color. The formula becomes:
This version is much easier to use. Change the values in G2, G3, or G4, and the result updates automatically. The custom "No match found" message is also helpful because it is much friendlier than a mysterious #N/A error staring at you like you forgot its birthday.
XLOOKUP with Two Criteria
Two-criteria lookups are common in business spreadsheets. You might need to match both a customer and an invoice number, a product and a region, or an employee and a month.
Here is a simple two-criteria formula:
In this example, Excel checks column A against F2 and column B against G2. If both conditions are true in the same row, XLOOKUP returns the value from column C. This is a clean alternative to older INDEX MATCH array formulas and often easier to read once you understand the pattern.
XLOOKUP with Three or More Criteria
Need more than two conditions? No problem. Add more logical tests and multiply them together:
This formula uses four criteria. As long as all lookup arrays have the same number of rows, Excel can evaluate them together. The important rule is consistency: the criteria ranges and return range must be the same size. If one range has 199 rows and another has 200, Excel may return an error or behave in ways that make you question your life choices.
Alternative Method: XLOOKUP with Concatenation
Another popular way to perform a multiple criteria lookup is concatenation. Concatenation means joining values together. Instead of checking several conditions separately, you combine criteria into one lookup value and combine lookup columns into one lookup array.
Here is the basic structure:
For example:
This formula joins the value in G2 with the value in G3, then searches for that combined value in the joined values from columns A and B.
Concatenation can be useful, especially if you are comfortable thinking in combined keys. However, it can create accidental matches if values run together. For example, “AB” plus “12” produces “AB12,” but “A” plus “B12” also produces “AB12.” To avoid this, use a delimiter:
The pipe symbol acts as a separator, making the combined key more reliable. Think of it as putting a fence between your criteria so they do not wander into each other’s yard.
Boolean Logic vs. Concatenation: Which Is Better?
Both methods work, but Boolean logic is usually the better choice for advanced Excel users because it is more flexible. Boolean logic makes it easier to add conditions, use comparison operators, combine AND and OR logic, and avoid accidental text collisions.
Concatenation is simple and readable for basic exact-match lookups, especially when you already have a helper column that combines fields. But if you are building a formula from scratch, Boolean logic is often cleaner and more powerful.
Use Boolean logic when you need flexible conditions, numeric comparisons, dates, OR logic, or no helper column. Use concatenation when your lookup criteria are simple text values and you want a straightforward combined-key approach.
How to Use OR Logic with XLOOKUP Multiple Criteria
Most multiple criteria formulas use AND logic, meaning all conditions must be true. But sometimes you need OR logic. For example, you may want to find a product where the region is either East or West.
In Boolean logic, multiplication means AND, while addition means OR. Here is an example:
This formula finds the first row where column A matches G2 and column B is either East or West. The parentheses are important. Without them, Excel may evaluate the logic in the wrong order, and your formula may behave like a GPS that insists the grocery store is in the ocean.
Using XLOOKUP with Numeric Criteria
XLOOKUP with multiple criteria can also handle numbers. For example, suppose you want to return a discount rate based on product category and a minimum order quantity.
This formula checks whether the category matches F2 and whether the quantity threshold in column B is less than or equal to the value in G2. For pricing tiers, shipping rates, commissions, and performance bonuses, this kind of lookup can save a lot of manual work.
Using XLOOKUP with Dates
Date-based lookups are common in finance, HR, project management, and sales reporting. For example, you may need to find a rate based on an employee name and a specific effective date.
The final argument, -1, tells XLOOKUP to search from last to first. This is useful when your table is sorted by date and you want the most recent matching record. In plain English, Excel starts at the bottom and works upward until it finds the first row that matches your conditions.
Returning Multiple Columns with XLOOKUP
XLOOKUP can return more than one column when the return array includes multiple columns. This is helpful when you want to return several related values from the same matching row, such as price, supplier, and lead time.
If you are using Microsoft 365 or another dynamic array version of Excel, the result can spill into neighboring cells. Before using this approach, make sure the cells to the right are empty. Otherwise, Excel may show a spill error, which is its dramatic way of saying, “Please move your stuff.”
Common Errors and How to Fix Them
1. The Ranges Are Different Sizes
Every criteria range and return range must have matching dimensions. If your lookup arrays use rows 2 through 100, your return array should also use rows 2 through 100.
2. Extra Spaces in the Data
Text values that look identical may not actually be identical. Extra spaces can break a lookup. Use TRIM to clean data when needed:
3. Numbers Stored as Text
If one column stores numbers as text and another uses real numbers, Excel may not see them as equal. Convert the data consistently before blaming the formula. The formula is not always innocent, but in this case, it may be.
4. Duplicate Matches
XLOOKUP returns the first matching result by default. If multiple rows match your criteria and you need all results, consider using the FILTER function instead. XLOOKUP is excellent for one result; FILTER is better when you want a list of every match.
When to Use FILTER Instead of XLOOKUP
If your goal is to return all matching rows, use FILTER. For example:
This formula returns every row where column A matches G2 and column B matches G3. XLOOKUP is best when you want one matching value or one matching row. FILTER is best when you want multiple matching records.
Best Practices for XLOOKUP with Multiple Criteria
Use structured Excel Tables whenever possible. Tables automatically expand as you add new data, and formulas with table references are easier to understand. For example, a table formula might look like this:
This version is more readable than a formula packed with cell ranges. It also reduces the chance of accidentally leaving new rows outside your formula.
Keep criteria cells clearly labeled. If G2 is product and G3 is region, label them. Future you will appreciate this. Future you has enough problems already.
Avoid full-column references in large workbooks when using array logic. Formulas like A:A=G2 can work, but they force Excel to evaluate more than a million rows. Use realistic ranges or Excel Tables for better performance.
Practical Example: Product Price Lookup
Imagine you manage a product list with columns for Item, Size, Color, Region, and Price. Your manager asks for a tool where users can choose an item, size, color, and region, then instantly see the correct price.
Your formula could be:
This formula gives you a clean, flexible lookup system without helper columns. It is excellent for catalogs, quote sheets, order forms, inventory tools, and dashboards.
Practical Example: Employee Bonus Lookup
Suppose you have a bonus table with Department, Role, Rating, and Bonus Amount. You want Excel to return the correct bonus based on three criteria.
This saves time and reduces manual errors. Instead of scanning tables, copying values, or building a maze of nested IF formulas, you let XLOOKUP do the work. Your spreadsheet becomes faster, cleaner, and slightly less likely to cause forehead wrinkles.
Experience-Based Tips for Using XLOOKUP with Multiple Criteria
After working with lookup formulas in real business spreadsheets, one lesson becomes obvious: the formula is only as good as the data behind it. Many XLOOKUP problems are not actually XLOOKUP problems. They are data consistency problems wearing a fake mustache. Before building a complex formula, check whether your lookup columns use consistent spelling, formats, capitalization, and spacing.
For example, “North East,” “Northeast,” and “NE” may all mean the same thing to a human, but Excel treats them as different values. If your formula returns “No match found,” do not panic. First, inspect the source data. Standardize region names, product codes, dates, and category labels. A small data cleanup session can save hours of formula troubleshooting.
Another practical habit is to build your formula one condition at a time. Do not start with a five-criteria formula and hope for spreadsheet glory. Begin with one condition. Confirm it works. Add the second condition. Confirm again. Continue until the full formula is complete. This step-by-step approach makes errors much easier to find.
You can also test the lookup array separately. For example, enter this part of the formula in a blank area if you want to understand the logic:
In dynamic array versions of Excel, this will spill a list of 1s and 0s. A 1 means the row matches all conditions. A 0 means it does not. This is one of the best ways to “see” what the formula is doing. Once you see the pattern, XLOOKUP with multiple criteria becomes much less mysterious.
In professional workbooks, I also recommend using named ranges or Excel Tables. A formula like Sales[Product] is easier to understand than A2:A5000. When someone else opens your file, they can read the formula without needing a treasure map. Cleaner formulas are easier to audit, easier to maintain, and easier to trust.
Be careful with duplicate records. If your data has two identical matches, XLOOKUP returns only the first match unless you change the search direction. If the “first match” is not necessarily the correct match, add another criterion such as date, version, location, or status. Multiple criteria should make the result unique. If they do not, your lookup may be technically correct but practically risky.
For large files, performance matters. Multiple criteria formulas evaluate arrays, so thousands of formulas across huge ranges can slow down a workbook. Use Excel Tables, limit ranges to the actual data size, and avoid unnecessary full-column references. A formula that checks 5,000 rows is usually much better than one that checks 1,048,576 rows just because it felt ambitious.
Finally, write formulas for humans, not just for Excel. Add labels, use clear input cells, and include helpful not-found messages. A formula like "No matching price" tells the user what happened. A raw #N/A error simply raises the emotional temperature of the room. The best spreadsheets are not just accurate; they are understandable.
Conclusion
Learning how to use XLOOKUP with multiple criteria in Microsoft Excel is one of the fastest ways to upgrade your spreadsheet skills. With Boolean logic, you can match several conditions at once and return the exact value you need. With concatenation, you can build simple combined-key lookups. With structured tables, clean data, and smart error handling, you can turn complicated lookup tasks into formulas that are powerful, readable, and reliable.
XLOOKUP is not just a replacement for VLOOKUP. It is a better way to think about lookups in modern Excel. Whether you are building reports, pricing tools, dashboards, employee trackers, inventory systems, or financial models, multiple criteria XLOOKUP can help you work faster and make fewer mistakes. And honestly, any formula that reduces spreadsheet drama deserves a little applause.
