Vlook Again -- Oh No

[Frank Bacchus]

Hi all:

This may be a simple answer:

I have an excel sheet with a key and a number. The only thing is the key repeats and the number may be different. I would like to do a vlookup and get (as a result) the highest number for that key. For example:

key - number
1 - 4
1 - 5
1 - 7 My vlookup result should be 7
2 - 10 My vlookup result should be 10
3 - 2 My vlookup result should be 2
4 - 6
4 - 7
4 - 9
4 - 11 My vlookup result should be 11

Simple - what is the vlookup formula?

thanks

frank

 

[azumi]

Try upload your excel file for better picture and describe your expected results

 

[Frank Bacchus]

Thanks. I have uploaded a file. Thanks for your input.

frank

 

[pecoflyer]

Something like =IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))
Pull down as needed

 

[Frank Bacchus]

Thanks pecoflyer. I could get this to work. Not sure I understand it. Can you please amplify a little or place in sample spreadsheet? Would appreciate it. Thanks.

 

[bosco_yip]

Something like =IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))
Pull down as needed

Hi pecoflyer,

Your formula appear incomplete, maybe missing........

=IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$9,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))

Regards
Bosco

 

[bosco_yip]

Thanks. I have uploaded a file. Thanks for your input.

frank

Or............

In E2, copied down :

=IF(COUNTIF($A$2:$A2,A2)=ROUNDUP(COUNTIF($A$2:$A$39,A2)/2,0),AGGREGATE(14,6,$B$2:$B$39/($A$2:$A$39=A2),1),"")

Regards
Bosco

 

[pecoflyer]

Hi pecoflyer,

Your formula appear incomplete maybe missing........

=IF(B1<>MAXIFS($B$1:$B$9,$A$1:$A$9,$A1),"",MAXIFS($B$1:$B$9,$A$1:$A$9,$A1))

Regards
Bosco

You're quite right, I deleted the 9 when replacing the semi colon with a comma.
Thanks

 

[Frank Bacchus]

You're quite right, I deleted the 9 when replacing the semi colon with a comma.
Thanks

Bosco & pecoflyer: Thanks a lot. this works like a charm. Appreciate it.

frank