Hello,

I currently am using a rank formula based on two criteria followed by a
sort in a macro.
Basically, I have built this to rank my sales for Best Apple Sales but
No Banana Sales Or Best Banana Sales but No Cherry Sales.
To do this I have dedicated many columns and I am trying to reduce the
need as I have 8 criteria and all the possible combinations
Best A, No B
Best A No C,
Best A, No D, etc

As a trial that didn't work, I put the criteria into two vlookup
charts, one called BestChart, the other NoChart

Here is a formula I tried:
=IF(AND(VLOOKUP(BK21,BestChart,2,FALSE),VLOOKUP(BK 26,NoChart,2,FALSE)),RANK(VLOOKUP(BK21,BestChart,3 ,FALSE),VLOOKUP(BK21,BestChart,4,FALSE),0),"")

Can anyone suggest anotehr way to do this?

Glen