Index/match return multiple results

tsand123

I have a sheet to track invoices for projects. There is the possibility to have multiple invoice per project. I would like to use index/match to bring that data into another sheet, but I would like to have a separate row for each invoice number. Currently my index/match will only return the first instance of the invoice number, see cells circled in red below. Is there a way to return consecutive values individually?

BullandKhmer

You probably want to use the mid fuction:

MID( text, start position, num chars )

This would likely only work long term if all invoice numbers were 8 digits.

You might need a helper column to calculate the correct start position (1,9,17,... etc), so....

=MID(Invoice#2@row, helpercolumn@row, 8)

Hope that helps.

Adam.

tsand123

I forgot to say that the Invoice # 2 column is using a Join/Collect function to return all the invoices in the reference sheet. Invoices numbers are in individual cells on the reference sheet. If there a way to use the value in the previous cell to tell the formula to move to the next result?