Excel: Attempting to have 2 tables of different size communicate without a #SPILL error-CodePudding

I run into the error because I am trying to make a string of formulas that read when between 2 dates in "table 1" is negative it pulls information from "table 2" to have it highlighted on the graph as a data callout. Biggest problem I'm having is since its attempting to read from 2 different sized arrays it outputs a #SPILL error. I would like to have "table 1" read the outputs from "table 2" and exclude Cells if it has already called it out before. The Index-Match function is the closest I've come but it only relays 1 cell of information and fails to update as the information is added into either "table 1" or "table 2".

Table1

Table2

Graph

I do apologize if I didn't explain this well enough but any help is appreciated.

Match-Index function worked well but failed to update as a more information was added into the tables

Is there any way to have excel exclude cells that were already called out?

CodePudding user response：

Delays[Reason] wants to return three rows - but there is not enough space to spill down - because next cell is blocked by the next formula. Therefore you receive the error.

You have to retrieve the delays reason for the special date.

As I don't know if there could be overalpping events for delays I added the TEXTJOIN function to merge two or more reasons into one cell.

=IF([@delta]<0,
        TEXTJOIN("; ",TRUE,
                  FILTER(Delays[reason],([@date]>=Delays[delay began])*([@date]<=Delays[delay ended]),"- no reason found")
        ),"")