Forum Discussion
Vector Cross Reference - Vector Lookup
What do you want to lookup where?
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.
