INDEX MATCH Formula help

H.Woods

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.

H.Woods

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)))

H.Woods

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)))