Compare & combine files · 5 min read

XLOOKUP Return All Matches? Use FILTER for Every Matching Excel Row

A customer can have several orders, so looking up that customer once does not produce a complete order report. Returning several columns from one matched order is also different from returning every order. This guide makes those shapes visible and builds an all-matches result with a second optional status condition.

JHSOFT editorialUpdated September 30, 2026

WORKED EXAMPLESynthetic data

order,customer,status,amount
O-101,C-7,Open,20
O-102,C-8,Open,30
O-103,C-7,Closed,15
O-104,C-7,Open,25
O-105,C-9,Open,10
O-106,C-7,Open,20

For C-7, the all-matches report contains four orders totaling 80. Adding status Open returns O-101, O-104, and O-106 totaling 65. O-101 and O-106 remain distinct orders even though their amounts match.

Download all-matches-input.csv ↓Download all-matches-expected.csv ↓Download open-matches-expected.csv ↓Download all-matches-summary-expected.csv ↓

Decide whether the output is one record or a set of records

Use Microsoft 365, Excel 2024, or Excel 2021 for the formulas below. Put the supplied orders in A1:D7, and enter C-7 in G2. Keep one row per order, with order ID, customer code, status, and numeric amount as separate fields. The source contains six orders in total, four of which belong to the chosen customer.

If your task asks for a single customer attribute, a lookup can be appropriate after verifying that the source key is unique. If your task asks for that customer’s orders, a one-row result omits information. Start by counting the expected orders manually in the tiny fixture: O-101, O-103, O-104, and O-106 are all required.

See why returning several columns is not returning every match

In I10, enter the XLOOKUP formula below with room for four columns. For C-7 the expected result is one row containing O-101, C-7, Open, and 20. The source already contains three other C-7 rows, but this formula has selected one matching order rather than assembled the customer’s complete order set.

Changing the return array from one column to four increases the width of that selected record. It does not change the task into a search for every source occurrence. Similarly, changing search direction chooses a different occurrence rather than creating a list of all occurrences. Keep this comparison below the main report so it cannot obstruct the report’s expansion.

I10: one matched row, four returned columns

=XLOOKUP(G2,B2:B7,A2:D7,"No matching order",0)

XLOOKUP returns the fields of one selected order, while FILTER returns four customer orders. — Illustrated example. The output width and the number of matching records are independent choices.

Build the all-matches report with aligned ranges

Put the four source headers in I1:L1 and enter the following formula once in I2. The returned record range and customer-condition range both cover rows 2 through 7. A condition array beginning on row 3 would describe different records, so keep the boundaries aligned whenever you edit this example.

The expected output fills I2:L5 in source order. Check all four order identifiers and add their amounts: 20 plus 15 plus 25 plus 20 equals 80. Comparing both row count and amount total is useful because two mistakes can occasionally cancel numerically. A report containing the right total but the wrong order identifiers is still incorrect.

I2: every matching customer order

=FILTER(A2:D7,B2:B7=G2,"No matching orders")

Add a second condition without discarding legitimate repeats

Enter Open in G3 and replace I2 with the two-condition formula. The customer and status tests are combined for each source row, so only rows satisfying both remain. O-103 is Closed and leaves the report. The result becomes O-101, O-104, and O-106 with a total amount of 65.

O-101 and O-106 have the same customer, status, and amount. They are nevertheless different source orders because their order identifiers differ. Do not add UNIQUE to the amount column just to make the report shorter: that would remove a legitimate transaction. First define the record identity and the reason any duplicate removal would be justified.

I2: customer AND status

=FILTER(A2:D7,(B2:B7=G2)*(C2:C7=G3),"No matching orders")

The status condition removes the closed order and preserves all three open orders, including two separate amounts of twenty. — Illustrated example. Equal amounts do not make two different order IDs the same transaction.

Test no matches, spelling differences, and blocked output

Set G2 to C-404, a customer absent from the fixture. The report should show No matching orders rather than suggesting that an order with amount zero exists. Set G2 back to C-7 afterward. Keep the no-match message outside any downstream calculation that assumes every result is a four-column order record.

For real identifiers, establish a consistent text type and a space policy before comparing. These synthetic customer codes are clean uppercase text. This guide does not promise case-sensitive matching for differently cased identifiers. If the result area shows a spill error, move notes or other reports out of the required rectangle instead of narrowing the source until the error disappears.

Choose an output layout you can refresh safely

The six-row fixture limits the maximum report height, but a live export can grow. Reserve a dedicated result area and keep explanatory notes above it or on another sheet. Update all source and condition bounds together, or use a reviewed Table reference for the growing source while keeping the spilling report in an ordinary range.

The downloads include both all-matches and open-only expected records, so a reviewer can trace the selection without trusting a screenshot. Independent Python checks validate record membership, order, and totals; they do not execute Excel. Test the final workbook in the recipient’s actual version, including its no-match state and the largest expected result, before depending on it for a recurring report.

Common questions

Can XLOOKUP return multiple columns?

Yes, it can return several fields from a selected matching row. That is different from enumerating all source rows matching a single customer; this guide uses FILTER for that task.

Why does my all-matches report contain the same amount twice?

Two separate orders can legitimately have the same amount. Preserve transaction identifiers and only remove repeated records after defining what makes a true duplicate.

Does this formula work in Excel 2016 or 2019?

The XLOOKUP and FILTER formulas in this guide target Microsoft 365, Excel 2024, and Excel 2021. Use a different reviewed workflow for editions that do not support them.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →