10 replies to this topic

[Conspire]

I'm doing an assignment on Excel and I need to list out the people who has the highest commission of the month using vlookup function. The problem is, I have more than 1 person and even if I added additional box to accomodate for the few of them and I am getting the same name in those 3 boxes that list out the names. So what I want to achieve here is to have the vlookup function listing out 3 different person with the same salary of the month. Hope I am not making anyone confused. Thanks for the help.

[kalasch]

Hi mate, :-) Can you give some example of your data and what you want on basis of these data ? Also, please mention your Office version. Solutions may sometimes differ from one version to another...

[Conspire]

I'm using 2007 here is the screenshot



As you can see the staff of the month, the names are the same. Right now I have 3 different people who are having the same amount of the salary.

Thanks

[kalasch]

Well, here I have a problem. I don't see the structure of your file. Thus, I don't have any idea of your search criterias, and of the sort order that you did use in your data. Can you put it on a file sharing platform and send me the link by PM, so that I can have a look at it ?

[Conspire]

PM sent

[kalasch]

Hi Conspire,

I come back to you, without any direct or simple solution helas...

The problem is that the VLOOKUP function is made to return a value in a range based on various criterias :

• a specific value being searched
• the column where this value must be searched (the first of a given range)
• the number of a column where the corresponding value has to be taken and returned.

There is another option giving the necessity to have a strict matching or not.

Well, in your case, you will want to find out a list of three (or even more, why not) names where a given value (salary) is equal to a given value (max of all salaries).

Bad news, with VLOOKUP alone, it's not possible. You'll have either the first or the last value...

But, what you want is possible !!

The idea is the following :
You can find the max value in a range (you have it in your J3 cell)
Given that, using the COUNTIF function, you can determine the number of cells containing the max salary.
Now, you'll have to make some presumption. How many employees can, on the same month, reach the highest value ? 5 ? 10 ? It's up to you to choose.
Admitting that there are 10 possibilities, we will create a specific array in the sheet to handle these 10 employees.
In the first column, numbers 1 to 10
In the second, a VLOOKUP call, on which we will come back a bit later. This column will handle the name.
In the third... oops, no third needed, we already know what we want...

Make a pause now, drink some glass of water or cup of coffee, and knock when you are ready...

OK. Ready ? Let's go...

Just a few lines above, i did say it was not possible to retrieve 'same' values on different rows in a table, and thus use of VLOOKUP is not possible... Indeed, to find different people having highest salary, all we will have to do is differentiate every row. How can we achieve that ?
The idea here is to add a unique value to the salary amount, if and only if salary is the same as the maxed one.
Well, let's start by adding two columns in the main range (by inserting just before I). Later, these columns will be hidden to keep the consistency in your sheet, but for now, only add the columns.

Now, we only have to build formulas as needed, to compute this unique number :
The value in the second row added is only a return of the name of employee. As VLOOKUP does need a first column to seek value, we will have to give him/her a 'second' column with the name...

Adding '1' is not enough to be unique, we have to aim adding 1 to the first row having max amount, 2 to the second and so on...
How to achieve that ? Well, not difficult... Just a bit of contorsionism :

• We want to add 1 if and only if amount is max.
• We want to add this value to the value computed on the previous line
• We, of course, want to have the max amount added as well

We have our first part :

IF (theLineAmount = theMaxAmount) Then
give back the value of : max + previousValue + 1
ELSE
... else what ?

We have two cases : the line before did contain max amount or not. If not, we only have to take back the previous value. Otherwise, we only have to reduce the max amount from this value.

And so, we can conclude :

IF (theLineAmount = theMaxAmount) Then
give back the value of : max + previousLineComputedValue + 1
ELSE
give back the value of : if (previousLineComputedValue > theMaxAmount) then give back the difference between the two values else the value of the previous line.

Well, there is still a problem in our thinking.
What happens if we get maxed values on two successive lines ?
max amount is added twice, without being 'resetted' between two lines...
Resolving is simple :

IF (theLineAmount = theMaxAmount) Then
give back the value of : max + if (previousLineComputedValue > theMaxAmount) then give back the difference between the two values else the value of the previous line + 1
ELSE
give back the value of : if (previousLineComputedValue > theMaxAmount) then give back the difference between the two values else the value of the previous line.

And now, a bit of refactoring, and we will return, for any cell, the following value :

Sum of :
1st part : if (theLineAmount = maxAmount) then maxAmount + 1
2nd part : if (previousLineComputedValue > MaxAmount) then give back the difference between the two values else the value of the previous line.

And now, that seems so clear :-)

Test it, and you will see, that works !

Now, we only have to make the lookup to show the names in our array. The one we speak about just before drinking a bit (wow, time for me to drink some again... back in a few seconds...

Done...)

So : the lookup now. We will number a list from one to ten. The value returned in front of each number is the result of the following eval :

if (theNumber <= theCountOfMaxedLines) then
give back the vlookup result based on theMaxAmount + theNumber.

So simple as that, isn't it ?

I hope that this will help you. I did not do the job in your file, as I presume you must do that for another classroom isn't it ? I just wanted to point you to the way of doing the things... giving you the logical behind.

Have a lot of fun with Excel, it's a great tool :-)

Best regards,




Edit : links used are safe and pointing to microsoft site :
VLOOKUP : http://office.micros...0698351033.aspx
COUNTIF : http://office.micros...0698401033.aspx

Edited by kalasch, 27 April 2009 - 03:00 PM.

[Conspire]

Thanks, I will let you know the outcome in a day or two.

[kalasch]

You're welcome...

[Conspire]

Hey, sorry I have some problem here..

We have our first part :

IF (theLineAmount = theMaxAmount) Then
give back the value of : max + previousValue + 1
ELSE
... else what ?

I'm quite new to Excel, so forgive me if I'm asking stupid questions. The quoted text, what am I suppose to put there?

[kalasch]

OK. Given that

• the cell M2 (i'll use strict references further : so $M$2) contains your MAX() function, retrieving the greatest amount in the list replace as needed $M$2 by the true reference
• you have defined the two new colums, becoming I (for the rectified amount for search) and J (to handle copy of the names)
• the column K contains the salary amount
• you are working on line 4 in cell I

Your formula should become, and this step :

Content of formula in cell I4 :
=IF(K4 = $M$2; $M$2 + I3 + 1; 0 real formula has to come later in the text.)

corresponding to

IF (theLineAmount = theMaxAmount) Then
give back the value of : max + previousValue + 1

Copy and paste in column...

I could give you the full solution, but as I did write in my previous post, I thing that, if it's for a course, you should learn by yourself the other parts.
I can try another way of showing things if you need it :-)

[Conspire]

Ok, thanks a lot for your time.