Match multiple values across several sheets and enter results on a different sheet

Hello all,
I have attached an example file, but the Issued sheet can contain over 200,000 rows so whatever we come up with will have to take that into account.

Several comparisons need to be made. The first is that the PARTNO (col B) on the Issued Sheet has to be matched to col D on the MaintPartNumber sheet. This means that the equipment on the Issued sheet must have maintenance performed on it.

The Issued sheet has the equipment that has been provided to various people.
The yellow highlighted rows are items that require maintenance, but no maintenance has been submitted (which is compared to the items on the MaintCompReport sheet).

The blue highlighted row does not require maintenance

The clear rows require maintenance and maintenance has been submitted (which is compared to the items on the MaintCompReport sheet).

The next comparison is between PartNo/SerialNo columns B and C in the Issued sheet with columns E and F on the MaintCompReport sheet.

For every item that requires maintenance as determined above, there should be at least one entry for each applicable maintenance on the MaintPartNumber sheet. So for example, if one of the items matches Part No 147 in Column D of the the MaintPartNumber sheet, that applicable serial number should have at lease one Mouse-1 AND one Mouse-2 maintenance entry in the MaintCompReport sheet.

If there is not a matching PartNo/SerialNo combination from the Issued sheet on the MaintCompReport sheet, then the results are copied to the MissingMaintReport sheet. This sheet currently contains the results of what I need the formulas to accomplish.

I tried VLOOKUP but it only grabs the first matching item so will not work here. I am not adverse to helper columns and have used them in the past if we need to use several formulas.

Any help is greatly appreciated.