Return invoice line item numbers on list of multiple invoices

Occasional Contributor

Hi All,

Currently importing data into our new software program. 

Is there is a formula that can search a column and return the line items of that particular invoice number?

Please see attached photo for further info: so I'm wanting Column A to read column B and return the line items for each invoice instead of me having to input manually.


3 Replies

@Natasha Townsend 

Enter this formula in A2, and copy down rows: 




@Natasha Townsend 


Try this formula:





@Natasha Townsend 

= 1 + IFERROR( (prior [LineItem]) /  ([@InvoiceNo] = (prior [InvoiceNo]) ), 0 )

where 'prior' is a Name given to the row of the table directly before the current row.

Defining prior to refer to

=OFFSET( Table1[@], -1, 0 )

works but makes the formula volatile.


Because the formula is likely to be unfamiliar, I have attached a file to demonstrate that it does address the stated problem.