SPILL Error on IF function

yup syntax was a typo, the sheet names are actually an indirect function to use a cell value to direct to the correct sheet, and when editing it to post here I missed removing a parentheses.

So I can't do ranges dynamic or otherwise due to the multiple sheets with varying sizes. (and a number of other reasons). I could make a macro that edits the formulas each time we refer to a different sheet, but thats extra work to create a workaround, rather than resolving the formula error.


I understand it's returning too many results, the question is why. There is only 1 row where D6 and C6 match. In fact there's only 1 row where d6 matches.

Also in the last formula I provided, I"m returning a singular string and not any array, yet there's still a SPILL error. If the first if is true, it returns the second if, which, if true returns ""hey it finally worked". The if's should either be true or false, and return the true value. But even when the true value is a string, its returning an array.