# Vlook Again -- Oh No

#### Frank Bacchus

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

frank

#### Attachments

• 16.9 KB Views: 9

#### pecoflyer

##### Active Member
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

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

##### Excel Ninja
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

Last edited:

#### bosco_yip

##### Excel Ninja

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

Last edited:

#### pecoflyer

##### Active Member
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

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