Selecting top Vendor Items based on conditions

You could do this with formulae only on Output tab however, they will not be efficient - and if your Input dataset is large performance will suffer. Given this fact I would advise using a couple of lightweight calcs on the Input tab which will then trivialise your Output calcs, and things will calculate very quickly. In more detail (and reflected in the attached)

1. Determine no. entries required per vendor (calc once per vendor)

Formula:

Please Login or Register  to view this content.

2. Create key for items to return where whole numbers reflect valid results and those with decimal remainder to be ignored

Formula:

Please Login or Register  to view this content.

note: per 2 - this returns numbers in ascending order thereby allowing for use of Binary Search approaches on your results (very fast)

3. Return results

Formula:

Please Login or Register  to view this content.

You should find that if you change contents of your Reference Table the results update instantaneously (on auto calc).