VLOOKUP WITH WEIGHTAGE

[FARAZ QURESHI]

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.

 

[Lars-Åke Aspelin]

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