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

#### spalton

##### Board Regular
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:

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:

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

Last edited:

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sorry the agent names are in column C

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!

Try:

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:
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:

and

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!

Your formula is an array formula, which you need to enter with Ctrl+Shift+Enter.

Basically the

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?

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?

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.

I'm struggling to understand your data to be honest. Are you clear on what your formula is doing so you can debug it?

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

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.

Replies
10
Views
465
Replies
5
Views
375
Replies
3
Views
292
Replies
5
Views
216
Replies
1
Views
158

1,219,915
Messages
6,150,939
Members
450,996
Latest member
darko1515s

### 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.

### Which adblocker are you using?

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

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