Multi-Criteria XLOOKUP in Google Sheets: How to Use It

In Google Sheets, use XLOOKUP with multiplied condition tests to find a row matching more than one criterion. With product names in A2:A100, regions in B2:B100, return values in C2:C100, and the requested product and region in H2 and I2, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=I2),C2:C100,"Not found",0). The formula returns the value from column C for the first row where both conditions are true.

What a multi-criteria lookup does

A standard lookup searches for one value, such as an order number, and returns a related value from another range. A multi-criteria lookup adds conditions: rather than finding every row for a product, for example, it can find the row for that product in a particular region and month. This is useful when no single column uniquely identifies a record.

XLOOKUP in Google Sheets accepts one lookup range and one search key, so the common technique is to make the lookup range an array of combined tests. Each comparison, such as whether a product name equals the requested product, produces TRUE or FALSE values. Multiplying the comparison arrays converts them into 1s and 0s: a row gets 1 only when every condition is TRUE. XLOOKUP then searches for 1 and returns the corresponding entry from the result range.

This is an AND lookup. If you need a match on either one condition or another, that is an OR problem and needs different logic; adding another multiplied condition would make the requirement stricter, not looser.

Build the basic XLOOKUP formula

Before entering a formula, identify the columns that contain the criteria and the column whose value you want back. In the example, each row is a product-and-region record. H2 holds the product to find, I2 holds the region, and the formula returns the matching value from column C. Keep the lookup ranges and result range the same height so their row positions line up.

With product names in A2:A100, regions in B2:B100, return values in C2:C100, the requested product in H2, and the requested region in I2, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=I2),C2:C100,"Not found",0). The final 0 explicitly requests an exact match. The fourth argument supplies a friendly result if no row matches, instead of displaying the usual not-found error.

Enter the criteria values in H2 and I2, or replace those cell references with the values or cells used in your sheet.
Check that each condition range starts and ends on the same rows as the return range.
Enter the formula in an empty cell and change the criteria to confirm that it returns the intended record.

For three conditions, multiply a third comparison into the lookup expression. With product, region, and year in A2:A100, B2:B100, and D2:D100, and requested values in H2, I2, and J2, the lookup array can be made from all three comparisons. Each additional test must describe the same rows as the others. If one range is shorter or shifted by a row, the formula may return an error or compare the wrong records.

Worked example and duplicate matches

Imagine a small table where column A contains product, column B contains region, and column C contains unit price. A2:C5 contains these records: Tea, North, 4.25; Coffee, South, 6.50; Tea, South, 4.75; and Tea, North, 4.40. If H2 is Tea and I2 is North, the basic formula returns 4.25, because the first matching row is the first Tea record for North. It does not average the matching prices or return all of them.

XLOOKUP returns one result. By default, its search proceeds from the first item to the last, so duplicate matches resolve to the first matching row. If the table is a log where the newest entry appears lower down and you want the latest matching record, specify a reverse search. With the same layout and criteria as above, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=I2),C2:C100,"Not found",0,-1). The last argument, -1, searches from the last row toward the first; in the example it returns 4.40.

That choice is a business rule, not just a technical detail. If duplicates are unexpected, check why the supposed key is not unique rather than silently relying on the first or last match. If the goal is to show every matching row, XLOOKUP is not the right output shape; use FILTER instead.

Using more criteria and different data types

Adding criteria is straightforward when each condition is a comparison against a cell. For instance, a table might have customer in A, status in B, and an invoice date in C, with the result in D. The tests can require the requested customer, a status such as Paid, and a date equal to the one in a criteria cell. Dates in Sheets are stored as date values, so compare actual date cells where possible rather than text that only looks like a date.

Text comparisons generally ignore case in Google Sheets, but spaces and inconsistent spelling still matter. A value with a trailing space may not equal the clean version in a criteria cell. If data arrives from forms or imports, normalize or clean it in the source data, and take care not to change meaningful spaces in names or identifiers. Numeric criteria should be numeric values, not text that resembles a number, especially when the sheet mixes imported and manually entered data.

Use bounded ranges such as rows 2 through 1000 when you know the table size. Whole-column references can be convenient, but they may require more work because the comparisons cover many empty cells. Whatever range style you choose, keep the criteria ranges aligned and the result range at the same height.

Common errors and how to diagnose them

No match appears: Confirm that every requested value exists in the same row, not merely somewhere in its separate column. Check for leading or trailing spaces, different date values, and numbers stored as text.
The wrong duplicate is returned: The default search finds the first match. Use reverse search only if the last matching row is the intended one, or investigate whether the identifying criteria should be more specific.
A range-size error appears: Make sure all comparison ranges and the result range have corresponding rows. A test over A2:A100 should not be paired with a return range that starts at row 3.
The formula shows a formula-parse error: Check parentheses, commas, and quotation marks. Google Sheets formulas in this example use commas between arguments; a spreadsheet configured for a locale that uses another argument separator may require the separator used by that locale.
The result is blank: A blank result cell is still a valid return value. Distinguish a matched blank from no match by using a visible missing-value message and checking the source row.

If cells in the criteria columns already contain errors, the comparisons can also produce errors that interfere with the lookup. Fix or handle those errors in the source data when practical. Avoid wrapping a large lookup in error-handling functions before understanding the underlying problem, since doing so can hide a broken range or an unexpected source error.

When FILTER or a helper key is a better fit

Use FILTER when you want all matching rows rather than one answer. With product names in A2:A100, regions in B2:B100, return values in C2:C100, and criteria in H2 and I2, this formula returns every matching value in column C: =FILTER(C2:C100,A2:A100=H2,B2:B100=I2). FILTER returns an array of results that can spill into cells below. If there are no matches, FILTER returns an error; you can handle that separately if a message is preferable.

A helper key is another option: combine fields into a dedicated column, then look up that combined key. It can make a sheet easier to inspect when users need to see or reuse the key, but delimiter choice matters. If a delimiter also occurs in the source values, different pairs can produce the same combined text. Multiplying comparison arrays avoids that particular delimiter ambiguity and keeps the source columns separate.

For a compact one-off lookup, direct multi-condition XLOOKUP is often easier to maintain. For repeated analysis, all matching rows, or more complex conditions, FILTER or a clearly labeled helper column may be easier for collaborators to understand. Choose based on whether the task needs one result or a set of results and how the sheet will be maintained.

Google Sheets and Excel differences

This formula is for Google Sheets in a web browser. Google Sheets supports XLOOKUP and the multiplication of comparison arrays used here. The function arguments are separated by commas in the examples, though locale settings can affect formula separators and number or date formatting. The Sheets interface uses browser menus; you do not need to enable an add-in or use a special lookup menu to enter the formula.

Microsoft Excel for Microsoft 365 also has XLOOKUP, and the central idea of multiplying condition tests is similar, but Excel and Sheets are separate products with their own function behavior, array handling, and locale settings. Do not assume that every spreadsheet feature or formula option transfers exactly between them. If you share a workbook or copy a formula between apps, verify the result in the destination program, especially when formulas use more advanced features.

Frequently Asked Questions

Can XLOOKUP in Google Sheets use two criteria?
Yes. Multiply two comparison arrays so a row produces 1 only when both tests are true, then search for 1. Add another multiplied comparison for each additional AND condition.

Does XLOOKUP return the first or last match?
By default, it searches from first to last and returns the first matching result. Set the search mode to -1 to search from last to first and return the last match.

How do I return every match instead of one?
Use FILTER with one condition argument for each requirement. FILTER can return multiple rows, while XLOOKUP is designed to return a single corresponding result.

Why does my multi-criteria lookup say Not found when the values look identical?
Look for hidden spaces, inconsistent data types, or dates that are displayed alike but store different values. Also confirm that all criteria match on the same record and that each range is aligned.

Can I use this formula for a date and a text condition?
Yes. Each test can compare a different field type, such as a date column and a text status column. Use real date values in the sheet and make sure the criteria cells contain the corresponding value types.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.