Vlookup formula criteria

[gmunro]

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(BK26,NoChart,2,FALSE)),RANK(VLOOKUP(BK21,BestChart,3,FALSE),VLOOKUP(BK21,BestChart,4,FALSE),0),"")

Can anyone suggest anotehr way to do this?

Glen

 

[Biff]

Assume your data looks like this for the comparison of "Best A, No B":

...........A.............B
1.......61............10
2.......57................
3.......95................
4.......44............50
5.......65................

Enter this formula in C1 and copy down to C5:

=IF(B1,"",SUMPRODUCT(--(B$1:B$5=""),--(A1<A$1:A$5))+1)

Will return these results:

...........A.............B............C
1.......61............10.............
2.......57...........................3
3.......95...........................1
4.......44............50.............
5.......65...........................2

Biff

 

[gmunro]

That looks much cleaner, thank you.
The problem I am trying to resolve is I would like people to click on a
"Best" radio Button and a "No" radio button.
They choose Best C, No E
or Best D No F
Then based on these two criteria I would like the macro to lookup the
required columns to complete the formula you provided
For instance
=IF(B1,"",SUMPRODUCT(--(VLOOKUP VALUE=""),--(A1<A$1:A$5))+1)

Where B$1:B$5, C$1:C$5 and D$1:5 are in the lookup chart

in essence, have the radio buttons rewrite the formula..

Is this possible?

Glen

 

[Biff]

I don't know enough VBA (yet!) to do this via a macro. Someone else will
have lend a hand.

Biff