Excel: Pull Data From a Table Based on Multiple Criteria

To pull a value from an Excel table using more than one condition, use XLOOKUP with a multiplied set of criteria; to return every matching row, use FILTER. For example, with an Excel table named Orders and the lookup values in H2 and H3, =XLOOKUP(1,(Orders[Customer]=H2)*(Orders[Region]=H3),Orders[Amount],"No match") returns the first matching amount. XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later.

How table-based lookups work

An Excel table stores related records in rows and labels each field with a column heading. A sales table might have columns named OrderID, Customer, Region, Status, and Amount. You can turn a range with headers into a table by selecting a cell in the data and choosing Insert > Table. Confirm that My table has headers is selected, then choose OK. On Windows, Ctrl+T is also a shortcut; on Mac, use Command+T.

Tables are useful for lookups because their structured references use names such as Orders[Customer] instead of fixed ranges such as B2:B500. When you add rows to a table, formulas that refer to its columns generally include the new records automatically. To name a table, select it and use the Table Design tab’s Table Name box. The examples below assume a table named Orders; replace its name and headings with the ones in your workbook.

A lookup has three basic parts: the value or condition to find, the column or array where Excel searches, and the value or row to return. The key choice is whether you expect one answer or several. A lookup function such as XLOOKUP returns one result, while FILTER can return every row that meets the conditions.

Return one value with XLOOKUP

Suppose Orders contains several customers and regions, and you want the amount for a customer in a particular region. Put the customer name in H2 and the region in H3. With data in the Orders table’s Customer, Region, and Amount columns, this formula returns the amount from the first row satisfying both conditions:

=XLOOKUP(1,(Orders[Customer]=H2)*(Orders[Region]=H3),Orders[Amount],"No match")

Each comparison produces TRUE or FALSE for every table row. Multiplying the comparisons converts TRUE to 1 and FALSE to 0; a row that meets both conditions produces 1. XLOOKUP searches for that 1 and returns the corresponding entry from Orders[Amount]. The fourth argument supplies a clear result when no row matches. Without it, XLOOKUP displays #N/A for a missing match.

For example, if the first matching record is for Ana in West and its Amount is 240, with H2 containing Ana and H3 containing West, the formula returns 240. If another Ana-in-West row appears lower in the table, XLOOKUP still returns the first match from top to bottom. Sort order therefore matters when duplicates exist and the first record is not the one you want.

For a single condition, the formula is simpler. With OrderID in H2 and the OrderID and Amount columns in Orders, use =XLOOKUP(H2,Orders[OrderID],Orders[Amount],"No match"). XLOOKUP uses exact matching by default, which is usually appropriate for IDs, names, and categories. You can also return text or dates instead of amounts by changing the return column.

Return every matching row with FILTER

If a customer can have several orders and you need to see all of them, use FILTER instead of a single-result lookup. Put the desired region in H2 and status in H3. With those criteria and the Orders table described above, this formula returns all table columns for rows where both conditions are true:

=FILTER(Orders,(Orders[Region]=H2)*(Orders[Status]=H3),"No matching rows")

FILTER returns a dynamic array: Excel places the results in the cells below and, if needed, to the right of the formula. The result updates when the criteria change or the table’s data changes. Leave enough blank cells for the results to spill. If an occupied cell blocks the output, Excel shows #SPILL!; clear the blocking cells or move the formula.

To return only selected fields rather than the whole table, make the return array a choice of columns. For instance, with OrderID and Amount as the columns you want, and with the same H2 and H3 criteria, use =FILTER(Orders[[OrderID]:[Amount]],(Orders[Region]=H2)*(Orders[Status]=H3),"No matching rows"). This range returns the columns from OrderID through Amount, including any columns between them. If the desired fields are separated by other columns, arrange a separate output or use a formula designed to select non-adjacent columns.

Combine criteria with AND or OR

Multiplication is a convenient way to require all conditions to be true. In the XLOOKUP and FILTER examples, multiplying the Region and Status tests means a row must satisfy both. Add another test by multiplying it into the condition array. With H2 holding the region, H3 the status, and H4 the customer, and with the corresponding columns in Orders, use =FILTER(Orders,(Orders[Region]=H2)*(Orders[Status]=H3)*(Orders[Customer]=H4),"No matching rows") to require all three.

For an OR condition, use addition between tests. With H2 containing either of the region names you want to include, H3 containing a second region, and Orders[Region] as the table column, =FILTER(Orders,((Orders[Region]=H2)+(Orders[Region]=H3))>0,"No matching rows") returns rows from either region. The greater-than-zero test makes clear that at least one comparison must be true. If your rule combines AND and OR, use parentheses to show which conditions belong together and check the result against a few hand-verified records.

Criteria cells can also hold dates, numbers, or text. Make sure the values have compatible types: a date stored as text may not match a real Excel date, and a number stored as text may not match a numeric value. For a date interval, the conditions can compare the table’s date column with a start date and an end date, then multiply those two tests to include dates within both boundaries.

Choose the right method

Use XLOOKUP when you want one corresponding value, such as the price for a product ID. It is concise and lets you specify what to display when no match exists.
Use FILTER when several records may qualify and you want to inspect or use all of them. Its results spill into neighboring cells and recalculate as the source or criteria change.
Use INDEX and MATCH when you need a lookup in an older Excel version that does not support XLOOKUP. It can return one match, but it does not by itself spill all matching rows.

There is no need to use FILTER just because the source is a table, or XLOOKUP just because the result is one cell. The deciding factor is how many matching records you need. If the criteria are not unique, a one-result formula silently chooses one record; a multi-result formula makes the duplicates visible.

Set up criteria and make results easier to maintain

Keep criteria in clearly labeled cells above or beside the result rather than typing changing values directly into a formula. For example, label G2 Customer and G3 Region, then enter the chosen values in H2 and H3. This makes the formula easier to reuse and lets another person change the lookup without editing its syntax.

Use consistent table headings and avoid blank rows or merged cells inside the source data. Each record should occupy one row, and each field should have one column. If the table has spaces in its name or a heading contains punctuation, Excel may format the structured reference differently; selecting the relevant table column while editing the formula is a reliable way to insert its reference.

For formulas that return multiple rows, place the formula outside the source table. Dynamic-array formulas cannot spill inside an Excel table. Also avoid putting notes or totals directly in the spill area. If you want the output formatted, apply formatting to a suitable range around the formula; spilled cells are results of one formula, not individually editable cells.

Compatibility and older Excel versions

XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later. Excel for the web supports these functions as well. Excel 2019 and earlier do not include them, so a workbook containing these formulas may not calculate correctly in those older versions. Mac users with a supported Microsoft 365 or Excel version can use the same functions and formulas; ribbon labels or placement can vary slightly by platform.

For a single match in an older version, INDEX and MATCH are a common alternative. With OrderID in H2 and the OrderID and Amount fields in the Orders table, use =INDEX(Orders[Amount],MATCH(H2,Orders[OrderID],0)). MATCH’s final argument of 0 requests an exact match, and INDEX returns the amount from the corresponding row. If the ID is absent, this formula returns #N/A unless you add error handling such as IFERROR.

Older Excel versions can also return multiple matches, but the formulas are more involved and may require copying a formula down or using legacy array-entry methods. If the workbook must work across different Excel releases, verify the target users’ versions before choosing a function. Another option is to filter the table using its column filter buttons when you only need to view matching records rather than build a formula-driven result.

Common problems and fixes

#N/A or the fallback text: Check spelling, leading or trailing spaces, and whether the lookup values have the same data type as the table values. For exact text matching, differences such as “North” and “North ” can matter.
#SPILL!: A cell in the dynamic array’s output area is occupied, or the formula is inside a table. Clear the obstruction or move the formula to a normal worksheet range.
Unexpected first result: More than one row meets the conditions. XLOOKUP returns the first matching row in search order; use FILTER to display all matches or add another criterion to make the lookup unique.
Formula does not update as expected: Confirm that the source is an Excel table and that new records were added within it. Check that the formula references the intended table and column names.
Formula displays as text or returns an error: Make sure the cell is not formatted as Text and that the formula begins with an equals sign. In some regional settings, Excel uses semicolons rather than commas as argument separators; follow the separator used by your installation.

When troubleshooting, test each condition separately in an unused cell or inspect a few source rows manually. That helps distinguish a formula problem from a mismatch in the underlying data. For example, check whether a date is truly stored as a date, not text that merely looks like one, before changing the lookup formula.

Frequently Asked Questions

How do I pull data from a table in Excel based on two criteria?
Use XLOOKUP with multiplied comparisons for one result, such as =XLOOKUP(1,(Orders[Customer]=H2)*(Orders[Region]=H3),Orders[Amount],"No match"). Use FILTER with the same conditions if you need all matching rows.

Can Excel return multiple rows that match my criteria?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER returns every row that meets its condition and spills the results into nearby cells. Keep the output area clear so the results can appear.

Does XLOOKUP return the first or last match?
By default, XLOOKUP searches from first to last and returns the first match. Its search mode can be changed to search from last to first, but if you need to review every duplicate, FILTER is clearer.

Can I search using a partial text match?
Yes. XLOOKUP supports wildcard matching when its match mode is set to wildcard mode. For example, with a search fragment in H2 and names in Orders[Customer], =XLOOKUP("*"&H2&"*",Orders[Customer],Orders[Amount],"No match",2) finds the first customer name containing that fragment. A question mark represents one character, and an asterisk represents any sequence of characters.

Why does my lookup return no match even though the value looks the same?
Hidden spaces, text-versus-number differences, and dates stored as text are frequent causes. Compare the underlying values and clean or standardize the source data before adding increasingly complex formula logic.

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.