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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!