Index/match return multiple results

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?


Answers

  • BullandKhmer
    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.

  • 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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!