VLOOKUP and MAX IF. Is this possible?

[Boycopus]

Hello. I hope somebody can help me on this one. I have been trying this for a day or so and looked up on google and this site to no luck.

I want to be able to run a VLOOKUP (or alternative) after creating a subset through MAXIF. Here is an example with some data. I can use the MAXIF function to tell me that 300 is the top score by an English competitor, and that 155 is the top score by a Spanish competitor but I want a formula that tells me that Alan is the top English competitor and that Charles is the top Spaniard, and that Greg is the top Frenchman.

A - B - C (A1:C8 Data Table)
Alan - 300 - England
Brian - 180 - England
Charles - 155 - Spain
Don - 200 - France
Edgar - 12 - Guinea
Francis - 13 - Spain
Greg - 210 - France
Harry - 222 - Haiti

Many thanks

BC

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[VBA Geek]

if excel 2010 or later:


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999E+307,ROW(<font color="Red">B1:B8</font>)/(<font color="Red">(<font color="Green">C1:C8=E1</font>)*(<font color="Green">B1:B8=AGGREGATE(<font color="Purple">14,6,B1:B8/(<font color="Teal">C1:C8=E1</font>),1</font>)</font>)</font>),A1:A8</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	A	B	C	D	E
1	Alan	300	England		France
2	Brian	180	England
3	Charles	155	Spain
4	Don	200	France		Greg
5	Edgar	12	Guinea
6	Francis	13	Spain
7	Greg	210	France
8	Harry	222	Haiti
Sheet3

 

[ghrain22]

if excel 2010 or later:


Worksheet Formulas

Cell	Formula
E4	=LOOKUP(9.99999999999E+307,ROW(B1:B8)/((C1:C8=E1)*(B1:B8=AGGREGATE(14,6,B1:B8/(C1:C8=E1),1))),A1:A8)

<tbody>
</tbody>

Excel 2013

	A	B	C	D	E
1	Alan	300	England		France
2	Brian	180	England
3	Charles	155	Spain
4	Don	200	France		Greg
5	Edgar	12	Guinea
6	Francis	13	Spain
7	Greg	210	France
8	Harry	222	Haiti

<tbody>
</tbody>

Sheet3​

You could also do this with an array formula
(assuming the same setup as VBA Geek with some implied named ranges):

=INDEX($A$1:$C$8,MATCH(MAX(IF(country=$E$1,score,"")),score,0),1,1)
Enter with ctrl shift enter...not just enter

I often see this:
9.99999999999E+307
on the forum, but don't know what it means, would someone mind explaining

 

[VBA Geek]

yes but if possible i prefer to avoid CSE

9.99999999999e+307 is the biggest number excel understands, so the lookup function returns the last number in the array

Watch:
https://www.youtube.com/watch?v=N24cBM-CTw4

You could also do this with an array formula
(assuming the same setup as VBA Geek with some implied named ranges):

=INDEX($A$1:$C$8,MATCH(MAX(IF(country=$E$1,score,"")),score,0),1,1)
Enter with ctrl shift enter...not just enter

I often see this:
9.99999999999E+307
on the forum, but don't know what it means, would someone mind explaining

 

[ghrain22]

Thanks

 

[Boycopus]

You could also do this with an array formula
(assuming the same setup as VBA Geek with some implied named ranges):

=INDEX($A$1:$C$8,MATCH(MAX(IF(country=$E$1,score,"")),score,0),1,1)
Enter with ctrl shift enter...not just enter
[COLOR=#0000FF]

[/COLOR][/QUOTE]

Many thanks for the replies. I couldn't get the first one to work but this one using INDEX and MATCH got me close. The only problem I have is when I expand it to 2000 rows it starts to report erroneous data. Imagine that Harry also scores 210, then it reports that Haiti's top scorer is Greg because it is the first row with 210 in it. Any ideas how we can get over this ?

thanks
BC

 

[VBA Geek]

could you not get mine to work because you are using an earlier version of excel or because you are using excel 2010 (or later) but the formula has an error?