IFERROR with INDEX and MATCH across multiple rows and sheets

Hi,

First time posting here, as I've exhausted my wife's Excel knowledge and mine. Google's searches seem to only bring me examples where people want numerical results, or are only dealing with a defined table set.
I am running Excel 2016 but it does not have the IFS capability.

I've recreated my problem set in a new smaller and simpler workbook for your guys' help. The original workbook that I need these formulas to work in has over 300 rows and 16 columns in the DR worksheet.

I want the Priority worksheet to pull in Rows from DR sheet that Match the criteria of being "Open" or "Closed - E", as it states in Column A.

I want the Priority list to also pull in Rows from the Upgrade sheet that Match the criteria of being "Upgrade", as it states in Column A.
We then want to be able to have a Column B or wherever that would be the Disposition or "ranking". Based on the ranking, let's say 1 through 5, we'd sort the Priority worksheet to help prioritize what items should be worked on.

As you can see, I tried using Helper columns to do an INDEX and MATCH for all items that met the criteria of being "Open" or "Closed - E" on the DR list, or "Upgrade" from the Upgrade Tracker sheet.

I went down the IFNA/IFERROR pathways and was only able to pull correctly from the Upgrade worksheet and not from the DR worksheet. Tried to insert OR nested within the formula after IFNA/IFERROR and also tried to throw that OR between the indexes for the two different worksheets.

I think my formula is getting jammed up trying to reconcile the helper index #'s from the DR worksheet and placing them appropriately into the Priority worksheet which is giving the win to the Upgrade worksheet.
I was also getting correct information for Column B under Disposition and Column D under Helper, but I've tweaked the formula continuing to troubleshoot why the Priority worksheet is only pulling data from one of the two desired worksheets, so now it is just giving me a "TRUE" response under Columns B and D.

Appreciate any help!

IFERROR formula used to pull data into 'Priority' worksheet: =IFERROR(OR(INDEX(DR!A:A,MATCH(ROWS(DR!$1:1),DR!$D:$D,0))),((IFERROR(INDEX(Upgrade!A:A,MATCH(ROWS(Upgrade!$1:1),Upgrade!$D:$D,0)),""))))

Helper formula used: IF(OR(A2="Upgrade"),1+MAX($D$1:D1),"")

Originally Posted by oeldere

I would place all data in 1 sheet.

After that you can analyse the data.

This can be done with a filter or a pivot table.

The Upgrade worksheet will only have a few select users editing fields, and they don't want to slog through 300+ rows of entries from a different team of users. This also doesn't really help with taking the data, condensing it and sending to another party who only needs to have the prioritized data.