Google Sheet Formula for finding an element across multiple tabs

I`m trying to populate values in column F, "Product Sold Date" in "TABLES" tab

Basically... the logic is as follows:

    1) If Column C (Product Status) = "Paused", then return "Paused"
    2) If Product start date = NULL or Product end date = NULL, then return NULL
    3) If Product start date < today`s date, then return "No Data"
    4) If Product start date >= today`s date, return "Upcoming"
    5) If product End date <= today`s date, return "Ended"
    6) If product start date <= today`s date, return "In Market"
7) If the condition does not belong to any of the above cases, then return the actual Product launch dates

Below is the link to the sample data I`m working on..

I`m pasting the link itself, becaues there are multiple tabs included

https://docs.google.com/spreadsheets/d/120rHOt8Pa_PdMKPgLkSYOKTO2Cv1hBH6PpTrg7FfJLk/edit?usp=sharing

Ultimately, I need to populate the actual "Product Launch Date" by scanning data in each tab

I tried using nested if statements with combination of Index Match. But I`m totally lost in case of multiple tabs

Can anyone please provide suggestion on this?

Should we think about using query statements instead in this case?

Sidenote: The returned values will be mix of dates and character [In market/ Ended/ Upcoming/ No Data/ NULL/ Paused/ Actual Date]