Can a =vlookup() consider multiple ranges of criteria?

I have a data set with 4 Columns of criteria A, B, C and D, ordered by priority level; each has a different Name.

The 5th Column D is of Revenues.

I need to build a summary that allocates the Revenues to certain names, based on the priority level, and based on whether or not the Name is one being considered.


Example: If Jane is in Column A, and Joe is in Columns B, C and D - Jane gets credit for the Revenues.

BUT: If Jack is in Column A, Joe is in Column B, and Jane is in Columns C and D - Joe gets credit for the Revenues, because Jack is not on the list of people we're considering for this summary.



I'm not great at explaining things, so I attached an image below that will hopefully be pretty descriptive and I can definitely answer any questions for clarification, or at least try my best

\1



Thank you greatly guys and gals, and I appreciate any help from this place as always