Function Problems

Return All Customer Invoice Numbers with an Excel Formula

Rana GarciaRana Garcia Nov 14, 2026 868 views

Question details

List every invoice number associated with a customer selected from a drop-down list.

Return All Invoice Numbers for a Customer in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Customer IDs and invoice numbers are stored in separate columns, and a selected customer determines which invoices appear.
Observed behavior
The desired result is a list of all invoice numbers matching the selected customer.
Before you start

Place customer IDs in A2:A100 and their corresponding invoice numbers in B2:B100, with the selected customer in F1. If F1 does not already have a drop-down list, prepare a separate range containing the customer choices.

Solution 1Recommended

Use INDEX and SMALL to return each matching invoice

Use this formula when you need a solution that can be copied down in older Excel versions.

1
Select a customer

Choose a customer in F1. To create a drop-down, select F1, open Data > Data Validation, choose List, and set Source to the range containing the customer choices.

2
Enter the formula

Select G1 and enter =IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$F$1,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($G$1:G1))),"").

3
Confirm and fill down

Press Enter, or press Ctrl+Shift+Enter in older Excel versions to confirm the array formula. Drag the fill handle from G1 down far enough to show all matching invoices.

Check the ranges: Adjust both data ranges together if your list extends beyond row 100. IFERROR returns a blank after the last match, but it can also hide other formula errors.
Work with invoices in WPS

List Customer Invoices in WPS Spreadsheets

Use an INDEX and SMALL array formula in WPS Spreadsheets to return invoice numbers for the customer selected in your worksheet.

  1. 1. Open the invoice sheet: Open your workbook in WPS Spreadsheets and check that customer IDs are in A2:A100 and invoice numbers are in B2:B100.
  2. 2. Choose a customer: Select a customer from the drop-down in F1, or type a customer ID into F1.
  3. 3. Enter the lookup formula: In G1, enter =IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$F$1,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($G$1:G1))),""). Confirm it with Ctrl+Shift+Enter if your WPS version requires array-formula entry.
  4. 4. Fill down: Drag the fill handle from G1 down to display each matching invoice in a separate row.
List multiple invoice numbers for one selected customer.Open and save Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface to work with formulas.Get started with a free office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDEX formula return only one invoice?

The formula returns one match per cell. Drag the fill handle from G1 down to retrieve additional matches.

When do I need to press Ctrl+Shift+Enter?

Older Excel versions require Ctrl+Shift+Enter to confirm the INDEX and SMALL array formula. In newer Excel versions, press Enter.

Why does FILTER show a spill error?

Cells in the output area below G1 may be occupied. Clear those cells and enter the FILTER formula again if necessary.

What happens when the selected customer has no invoices?

The example formulas return a blank. The INDEX formula also returns blanks after all available matches have been listed.