Function Problems

How to Return Multiple Invoice Numbers for One Purchase Order in Excel

Chanuka Geekiyanage Oct 9, 2026 872 views

Question details

The user needs to extract all matching invoice numbers associated with a specific purchase order from a dataset containing multiple vendor invoices.

How to Return Multiple Invoice Numbers for One Purchase Order in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating dynamic reports where a single purchase order acts as a lookup value that is associated with multiple different vendor invoices.
Observed behavior: Using XLOOKUP or VLOOKUP only returns the first matching invoice, failing to display the rest of the associated invoices for that purchase order.

Before you start

Ensure your dataset is organized in a tabular format without merged cells, and take note of the exact cell ranges containing your Purchase Order numbers and Invoice numbers.

Solution 1Recommended

Use the FILTER Function to Spill Multiple Matches

The FILTER function is the most efficient way to return an array of all matching records based on specific criteria, overcoming XLOOKUP's single-match limitation.

Dynamic array functions allow a single formula to return multiple values that automatically 'spill' into neighboring empty cells. The FILTER function will pull every invoice number that matches the purchase order criteria.

Select a blank cell

Click on the empty cell where you want the first matching invoice number to appear.

Enter the FILTER formula

Type `=FILTER(InvoiceNumbers, PurchaseOrderRange=A2, "No invoices found")`. Replace 'InvoiceNumbers' with the range containing your invoices, 'PurchaseOrderRange' with the range containing PO numbers, and 'A2' with the cell holding the PO number you are searching for.

Execute the formula

Press Enter. All matching invoice numbers will automatically spill into the cells below.

Use the FILTER Function to Spill Multiple Matches

Dynamic Arrays Requirement: The FILTER function requires a modern version of Excel (Microsoft 365, Excel 2021) or WPS Spreadsheet that supports dynamic arrays.

Solution 2

Combine Results into a Single Cell Using TEXTJOIN and IF

If you prefer to have all matching invoice numbers listed in a single cell separated by commas, you can use a combination of the TEXTJOIN and IF functions.

Efficient Data Filtering

Use WPS Spreadsheet to Easily Filter Multiple Records

WPS Spreadsheet fully supports dynamic array functions like FILTER and XLOOKUP, allowing you to seamlessly extract multiple invoice numbers for a single purchase order. It is a powerful, lightweight alternative to handle all your reporting needs.

1. Open your data file: Launch WPS Spreadsheet and open your existing Excel workbook containing the purchase orders.
2. Insert the FILTER formula: Select a blank cell and input `=FILTER(B2:B100, A2:A100=D2, "Not Found")`, where B is the invoice column and A is the PO column.
3. Press Enter to spill results: Hit Enter on your keyboard. WPS Spreadsheet will instantly calculate and spill all the corresponding vendor invoices into the neighboring cells.

Fully compatible with Microsoft Excel (.xlsx) formats and dynamic array formulas.Free to use with a familiar, easy-to-navigate tabbed interface.Lightweight and fast, making it ideal for handling large datasets and complex reports.Supports seamless file migration without formatting loss.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP only return one value?

By default, the XLOOKUP function is designed to stop searching and return the very first exact match it finds in the lookup array. It cannot output multiple separate matches on its own.

What if the FILTER function returns a #SPILL! error?

A #SPILL! error occurs when there is existing data blocking the adjacent cells where the formula needs to expand its results. Simply clear or delete the contents of the blocking cells to fix the error.

Can I sort the multiple invoice numbers returned by the FILTER function?

Yes. You can nest the FILTER function inside the SORT function, like `=SORT(FILTER(InvoiceRange, PORange=A2, ""))`, to automatically display the extracted invoice numbers in ascending or alphabetical order.

Does the FILTER function work in older versions of Excel?

No, FILTER is a dynamic array function available only in Microsoft 365, Excel 2021, and modern versions of WPS Office. For older versions, you must use alternative array formulas combining functions like INDEX, SMALL, and IF.

Excel & Data Problems