Return All Customer Invoice Numbers with an Excel Formula
Question details
List every invoice number associated with a customer selected from a drop-down list.

- 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.
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.
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.
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.
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))),"").
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.
Use FILTER to spill all matching invoices
Use FILTER in an Excel version that supports dynamic arrays to return the matching invoices automatically.
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. 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. Choose a customer: Select a customer from the drop-down in F1, or type a customer ID into F1.
- 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. Fill down: Drag the fill handle from G1 down to display each matching invoice in a separate row.

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.




