Vector Cross Reference - Vector Lookup

[EnglandPrevails]

I'm trying to adjust an existing vector formula that is working, to include an additional factor. The factor is based on a lookup of a parameter in the primary table. I've tried to find a way to do a lookup using a vector of values each of which should result in a single response.

Conceptually, it should be something like this:

=Sumproduct (A1:A1000,--(B1:B1000="JohnDoe"),[lookup](B1:B1000))

A and B are columns in the primary table, and the lookup is in a reference data table. I typically prefer to use Index/Match for my cross reference lookups, but I don't see how to use that here, since it typically returns a single value. How can I execute a vector of lookups and return to the function a vector of resulting cross reference values? I've used the notation "[lookup]" above simply to indicate that I don't know the structure for this kind of cross reference lookup.

Thanks

[#2]

@EnglandPrevails 

What do you want to lookup where?

[#3]

@Hans Vogelaar 

I don't want to overcomplicate my question, so I'll try to answer your question in terms of simplified analogous structure.

Let's say that my primary table has 1000 rows and shows sales activity.  Each sales activity (one row) is associated with a sales person, and each sales person is associated with a category of sales person.  The sales activity is measured weekly in $ of sales (many columns across the row). 

There's a secondary reference table with 100rows that lists all the sales people and indicates their commission % by week. 

What I'm attempting to do is to calculate for a given week the commission $, at the sales person category level.  I use SumProduct to sum across all rows relevant to that category of sales person, and then before aggregating I'm trying to multiply each individual sales person's sales activity by the corresponding commission % which is stored at the sales person level (not the category level).

Something like the following.  [Note that your question is prompting me to clean my original formula up a bit, especially now using my more detailed example.]

=Sumproduct ( ((Sales Activity in $ for a particular week) * (Commission % for that Sales Person during that week)) * (Filter by Sales Person Category) )

=Sumproduct ( (A1:A1000),([lookup](B1:B1000)),--(B1:B1000=Sales Clerk) )

Where "[lookup]" is a placeholder for a vector lookup of all Sales people in the primary table to find all their commission percentages for that week in the secondary table.

My question was whether a vector cross reference lookup of this type can be performed within a vector formula.