Attempting to Indirect(Index(Match())

So essentially I'm trying to take an index match that is converted into text through using formulatext and from there convert it back to. The larger idea is to use cell("Address") along with a bunch of mids,lefts, and lens to pull the sheet name of where the result is from.

Please let me know if using indirect outside of a formulatext(indirect(match()) is viable or if there is an alternative.

I should note that I've already set up a formula which would pull the sheet name for regular cells being referenced to other sheets. The trouble is for index match.

Thank You!

Please Login or Register  to view this content.

So this doesn't necessarily work for the event of if this then index from this sheet, if not then index from this other sheet. It does pull a sheet name, but the purpose would be to pull the sheet that the value is coming from.

Is there no way to convert an array formula into text and back into an array formula? You can do it for a regular formula using indirect(formulatext()), but I'm having trouble with the array formula.

Essentially trying to do the following:

=INDIRECT("{"&INDEX(Test!D3:D4,MATCH(Sheet1!C3,Test!C3:C4,0))&"}")