Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP WITH WEIGHTAGE
Question # 1 is:
I have a list of agents helping in selling multiple products. One sheet consists of Two columns i.e. Name & Item and on the other sheet only a single column of Names. Here, on this second sheet, I want to place a VLOOKUP sort of formula in front of each name of agent so as to reflect the product he is MOST involved in. For example the source may be as: Agent 1 Product A Agent 1 Product B Agent 1 Product B Agent 1 Product B Now if I place a VLOOKUP in front of Agent 1 in the other sheet only the first match would result, i.e. Product A whereas concentration in Product B is found. Another similar question is: Agent 1 Product A 10,000 Agent 1 Product B 20,000 How to insert a VLOOKUP sort of formula for showing Product B in front of Agent 1 because volumne of sales of Product B is greater than that of Product A. -- Best Regards, FARAZ A. QURESHI |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP WITH WEIGHTAGE
On Sat, 12 Jul 2008 01:00:00 -0700, FARAZ QURESHI
wrote: Question # 1 is: I have a list of agents helping in selling multiple products. One sheet consists of Two columns i.e. Name & Item and on the other sheet only a single column of Names. Here, on this second sheet, I want to place a VLOOKUP sort of formula in front of each name of agent so as to reflect the product he is MOST involved in. For example the source may be as: Agent 1 Product A Agent 1 Product B Agent 1 Product B Agent 1 Product B Now if I place a VLOOKUP in front of Agent 1 in the other sheet only the first match would result, i.e. Product A whereas concentration in Product B is found. Another similar question is: Agent 1 Product A 10,000 Agent 1 Product B 20,000 How to insert a VLOOKUP sort of formula for showing Product B in front of Agent 1 because volumne of sales of Product B is greater than that of Product A. For the first question I do not understand what you are after. For the second question, you may try this: I assume that agent name, product name, and product "figure" are in columns A, B, and C respectivley in a worksheet named Sheet1. If the Agent name is in B1 of the other sheet and you want the product with highest figure put the following in cell A1 of the other sheet =INDEX(Sheet1!$B$1:$B$10,MATCH(TRUE,((Sheet1!$C$1: $C$10)=MAX((- -(Sheet1!$A$1,$A$10=$B1)*(Sheet1!$C$1:$C$10)))),0)) Change the 10 in all places to suit the number of rows of your table in Sheet1. You may copy this formula down to handle more agents. Not that this will not handle the case like in question 1. There must only be one row in Sheet1 for each combination of Agent / Product, like in the second question. Hope this helps. / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to limit the Weightage of any data & distribute excess to othe | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |