INDEX MATCH Formula help
Hi I'm looking for help with a formula. I've gotten some of it written but I'm missing something. I have a "Mastersheet" where my form responses are held. Then depending on the form originator the row is copied to a new sheet. The new sheet will be where tasks are worked on and completed. I have a "Record Closed Out?" Check box and separate date column. What I'm trying to do is use Index Match to link the cells between the Mastersheet and the new sheet, so that when a record is completed (checkbox ticked) and a date added to the next column, that this updates the mastersheet.
This is the formula I have so far:
=IFERROR(INDEX({IQA Log Record Closed Out Date}, MATCH([Primary Column - IQA Ref]@row, {IQA Log Range - Ref. No.}, 0)), "Still Open")
I need the blank cell from row QA00009 to also return the statement "still open".
There is also a huge caveat to this - currently this is only one "source" sheet to one "destination" sheet but it will eventually be several source sheets all to one destination sheet. Is this possible? and will I need to add in the COLLECT function somewhere?
Thanks in advance.
Best Answer
-
I found a workaround - I added some additional columns and then used the following formula:
=IFERROR(IFERROR(IFERROR(INDEX({IQA Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{IQA Log IQA Ref No},0)),INDEX({Daily Quality Inspections Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{Daily Quality Inspections IQA Ref No},0))),INDEX({Re-Make Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{Re-Make Log IQA Ref No},0))),INDEX({CNC Mistakes Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{CNC Mistakes Log IQA Ref No},0)))
Answers
-
I found a workaround - I added some additional columns and then used the following formula:
=IFERROR(IFERROR(IFERROR(INDEX({IQA Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{IQA Log IQA Ref No},0)),INDEX({Daily Quality Inspections Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{Daily Quality Inspections IQA Ref No},0))),INDEX({Re-Make Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{Re-Make Log IQA Ref No},0))),INDEX({CNC Mistakes Log Record Closed Date},MATCH([Primary Column - IQA Ref]@row,{CNC Mistakes Log IQA Ref No},0)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!