Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup formula criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup formula criteria
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup formula criteria
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:D:5 are in the lookup chart in essence, have the radio buttons rewrite the formula.. Is this possible? Glen Biff wrote: 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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup formula criteria
I don't know enough VBA (yet!) to do this via a macro. Someone else will
have lend a hand. Biff "gmunro" wrote in message oups.com... 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:D:5 are in the lookup chart in essence, have the radio buttons rewrite the formula.. Is this possible? Glen Biff wrote: 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" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |