Looking up max value in a list but returning a different cell

spalton

Board Regular
Joined
Feb 20, 2009
Messages
125
Hi,

I have a list of Team Leaders, Agents, and the agents conversion rate.

The conversion rate is updated daily from telephony reports, and I manually sort it into conversion order each day before sending it round.

What I want to do is find the highest converter for each team.

So:

Column A ('TeamLeader' range) has the Team leaders in, column B has the agent name ('Agent' range), and column F has the agents conversion rate ('conversion' range).

I think I'm on the right track with this formula:

=(MAX(IF(TeamLeader="Bonnie",Conversion))

But this only displays the figure in the Conversion rate, how to I get it to 'step back' 4 column's and show the agents name?

I tried this:

=VLOOKUP(MAX(IF(TeamLeader="Bonnie",Conversion)),A5:F60,-4,FALSE)

but that returns #NA - is this because I'm asking the VLOOKUP to look 'backwards' 4 columns?

Thankyou! Hope this made sense!
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
After a bit of research I'm obviously a noob and perhaps need to use INDEX and MATCH to get this result, however I can't seem to nest the MAX function within the INDEX one. Can anyone offer any advice?

Thanks!
 
Upvote 0
Try:

=INDEX(TeamLeader,MATCH(MAX(Conversion),Conversion,0))

explanation:
This looks for the maximum value in the Conversion range and returns the position of that value within the Conversion range. The INDEX function then uses that position to return the value of TeamLeader at that same position.

e.g. max of conversion is 100, and 100 appears in the 4th cell within Conversion. The function returns the value of the 4th cell from the TeamLeader range.
 
Last edited:
Upvote 0
Hello again,

In addition to the MAX value, I also need the lowest converter for each team so they can be managed effectively. I amended the Max forumula to MIN, however, this only returns the first minimum converter across the whole department, it doesn't determine the lowest per team. For instance:

=INDEX(Agent,MATCH(MIN(IF TeamLeader="Bonnie",Conversion,"")),Conversion,0))

and

=INDEX(Agent,MATCH(MIN(IF(TeamLeader="Jake",Conversion,"")),Conversion,0))

both return the same agent, despite the fact they have a different team leader.

I can't understand why the IF(TL="Jake") doesn't determine who is in his team rather than just the lowest person on the floor?

Thanks so much for all your help on this issue!
 
Upvote 0
Your formula is an array formula, which you need to enter with Ctrl+Shift+Enter.

Basically the

MIN(IF TeamLeader="Bonnie",Conversion,""))

part is an array, since you are asking it to return the minimum value of the array of cells in Conversion where the TeamLeader value on the same row is ="Bonnie".

BTW, your named ranges are identical sizes, right?
 
Upvote 0
That said, you should have got a #VALUE! error when you entered it without C+S+E.....

And are you sure they haven't got the same Agent name?
 
Upvote 0
It's already entered as an array formula, and I'm sure the agent names are all different :)

we have 5 team leaders here, all with people that have currently scored low conversion rates, however on my 'Team table' it shows the miniumum converter as the same for every team, using the formula above.
 
Upvote 0
I'm struggling to understand your data to be honest. Are you clear on what your formula is doing so you can debug it?

=INDEX(Agent,MATCH(MIN(IF(TeamLeader="Bonnie",Conversion,"")),Conversion,0))

IF(TeamLeader="Bonnie",Conversion,""))
Searches the TeamLeader range and, for each occurence of "Bonnie", returns the corresponding value in Conversion. For other occurrences it returns a "". i.e. you get {3,"",5,"",2,""}

MIN
This determines the lowest value in that array, ignoring the text values

MATCH(...,Conversion,0)
returns the position/location of that minimum value within the Conversion range, e.g. 4th value (by position/location)

INDEX(Agent,...)
This now takes the value returned by the MATCH function, e.g. 4, and uses this to return the 4th value (by position/location) in the Agent range.

If it helps, use the Formula Evaluation tool to step into the calculation and understand where your results are coming from.

HTH
 
Upvote 0
that all makes sense, I think the prob is that several of the lowest converters have the same low conversion score, and it's just returning the first of these agents in the list for all the teamleaders, rather than correctly matching the agent to the correct teamleader.

when i manually manipulate the data so they have different scores it works, it's just when several agents have the same score the 'IF Teamleader = whoever' statement doesn't seem to work.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top