How To SheetsHow To Sheets
Formulas and Functions

XLOOKUP with multiple criteria in Google Sheets

Learn multi-criteria lookups in Google Sheets using INDEX/MATCH, FILTER, and XLOOKUP-like patterns. Step-by-step formulas, real-world examples, and tips for reliable results.

How To Sheets
How To Sheets Team
·5 min read
Search-Google-SheetsWhat-Is-Google-SheetsVlookup-Google-SheetsFormula-Errors-Google-Sheets
Multi-criteria XLOOKUP - How To Sheets
Photo by www.kaboompics.comvia Pexels
Quick AnswerSteps

To perform a multi-criteria lookup in Google Sheets, use INDEX/MATCH with a composite test or FILTER to combine criteria. Example: =INDEX(ReturnRange, MATCH(1, (Criteria1=Val1)*(Criteria2=Val2), 0)). This returns the first row that satisfies both conditions. If XLOOKUP is available, you can adapt with a boolean lookup vector similar to Excel.

Overview: Multi-criteria lookups with Google Sheets

Multi-criteria lookups let you pull a value based on more than one condition. In Google Sheets, that means testing two or more columns for matching values and returning the corresponding item from a return column. The keyword xlookup multiple criteria google sheets captures the typical scenario: you want to find a row that matches both a name and a category, or a product and a region, and you want a single, precise result. This approach avoids brittle single-key lookups and scales well as your data grows. You will gain reusable patterns you can apply to dashboards, student rosters, inventory sheets, and sales trackers. The goal is to produce a single, correct value with minimal fuss and robust behavior across blank or duplicate rows.

Practice-ready formulas and patterns

  • Use INDEX/MATCH with a composite test for precise row selection.
  • Use FILTER when you want to return multiple matching rows or a subset of data.
  • Consider XLOOKUP-like patterns if your environment supports boolean lookup vectors. Below are safe, copy-paste-ready examples you can adapt to your dataset.

Why this matters in daily work

When criteria multiply, simple VLOOKUPs fail or require fragile helper columns. The patterns shown reduce maintenance, give you predictable results, and help you audit calculations. Users often run into performance issues on very large sheets; the methods here are efficient when ranges are properly scoped and recalculation is minimized. With the examples, you’ll understand not just the formula, but the underlying logic so you can extend it to three or more criteria or to different return columns.

Quick-start checklist

  1. Identify the return column and at least two criteria columns.
  2. Decide between INDEX/MATCH and FILTER based on whether you need a single value or multiple results.
  3. Test formulas with edge cases (no match, multiple matches, blanks).
  4. Wrap in IFERROR to present a friendly message when not found.
  5. Extend to three or more criteria using the same boolean-multiplication pattern.

Method details: a quick map to choose your approach

  • INDEX/MATCH with multiple criteria is the classic approach and scales well for single-value lookups.
  • FILTER is ideal when you want to preserve all matches or when you expect multiple results; you can then wrap with INDEX if you need a single item.
  • XLOOKUP with boolean arrays is elegant if your Google Sheets environment supports it, but it’s not universally available across all accounts and locales. The next sections show concrete formulas for each method.

Common variations and alternatives

  • If you have numeric IDs that are unique, you can replace the criteria with a single joined key: (Criteria1&"|"&Criteria2). This can simplify debugging.
  • For three or more criteria, extend the boolean multiply: (A:A=crit1)(C:C=crit2)(D:D=crit3).
  • Use IFERROR to handle missing results gracefully: =IFERROR(formula, "Not found").

Steps

Estimated time: 15-25 minutes

  1. 1

    Identify criteria and return column

    List the two (or more) columns that define your criteria and the column that holds the value you want to return.

    Tip: Write down actual cell references to avoid confusion.
  2. 2

    Choose your approach

    Decide whether you want a single result (INDEX/MATCH or FILTER with [1]) or all matching rows (FILTER without INDEX).

    Tip: If duplicates are possible, plan how to handle them.
  3. 3

    Enter the formula

    Type the formula using the appropriate pattern for your data: INDEX/MATCH, FILTER, or XLOOKUP, with two or more criteria.

    Tip: Start with a small tested range to verify behavior.
  4. 4

    Test edge cases

    Check not-found scenarios (use IFERROR), single-match, and multi-match scenarios to validate resilience.

    Tip: Intentional blanks can break patterns; account for them.
  5. 5

    Extend to more criteria

    If needed, add another criterion by multiplying another boolean test or nest a FILTER condition.

    Tip: Keep formulas readable by breaking into helper cells if necessary.
Warning: Avoid wildly large ranges when possible; use explicit ranges to improve performance.
Pro Tip: Use absolute references (e.g., $A:$A) if you plan to copy formulas across rows.
Note: If data contains blanks, consider wrapping with IFERROR to return a friendly message.

Prerequisites

Required

  • Google Sheets (web or mobile) access↗
    Required
  • Basic familiarity with formulas (IF, logical operators, and ranges)
    Required
  • Understanding of absolute vs relative references when dragging formulas
    Required

Optional

  • Optional: XLOOKUP support in your sheet (if available)
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy a formula or valueCtrl+C
PastePaste into a cellCtrl+V
Fill downApply formula to below rowsCtrl+D
UndoRevert last actionCtrl+Z
Edit formula in placeModify current formulaF2
Search within sheetFind criteria or textCtrl+F

FAQ

What is a multi-criteria lookup in Google Sheets?

A lookup that matches more than one condition across columns, returning the corresponding value from a return column. Common patterns use INDEX/MATCH or FILTER with boolean tests.

A lookup that matches more than one condition across columns, returning the matching value.

Can I use XLOOKUP for multiple criteria in Google Sheets?

If your sheet supports XLOOKUP, you can pass a boolean array as the lookup vector to match multiple criteria. Otherwise, use INDEX/MATCH or FILTER.

Yes, if XLOOKUP is available, you can match multiple criteria with a boolean array.

What if there are multiple matches?

These methods typically return the first match. To capture all matches, use FILTER and array results or aggregate with functions like TEXTJOIN.

If several matches exist, you may need to aggregate results.

Which method is fastest for large datasets?

INDEX/MATCH and FILTER are generally efficient, but performance depends on data size and complexity. Consider limiting ranges or using helper columns to speed recalculation.

Performance depends on data size; try to limit ranges.

How do I handle not-found results?

Wrap the formula with IFERROR to return a friendly message instead of an error.

Use IFERROR to handle missing results gracefully.

The Essentials

  • Use INDEX/MATCH for robust multi-criteria lookups
  • FILTER can return multiple matching rows when needed
  • XLOOKUP with boolean arrays is possible where supported
  • Handle missing results gracefully with IFERROR
  • Extend criteria with additional boolean tests for three or more conditions

Related Articles

← More in Formulas and Functions