# Formula for Returning Highest Value in a Set

#### wilsork

##### New Member
Excel 2007
Windows XP Pro

Based on the Main Acct (Column B) and highest Unique Value (Column C) how can I retrieve a result of a Y (Column D) for the highest value for a Main Acct? Terrible explanation I feel like, but an example of what I am trying to accomplish is below. I've tried using a Max function, but to no avail. I have basic knowledge of Excel, but this might be well beyond my limitations. Any help provided would be extremely appreciated.

 Acct Main Acct Unique Result 123 11111 3 ???? 321 11111 2 ???? 443 11111 1 ???? 556 22222 1 ???? 487 33333 1 ???? 456 44444 5 ???? 789 44444 4 ???? 987 44444 3 ???? 654 44444 2 ???? 234 44444 1 ????

<TBODY>
</TBODY>
 Acct Main Acct Unique Result 123 11111 3 Y 321 11111 2 N 443 11111 1 N 556 22222 1 Y 487 33333 1 Y 456 44444 5 Y 789 44444 4 N 987 44444 3 N 654 44444 2 N 234 44444 1 N

<TBODY>
</TBODY>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Excel 2007
Windows XP Pro

Based on the Main Acct (Column B) and highest Unique Value (Column C) how can I retrieve a result of a Y (Column D) for the highest value for a Main Acct? Terrible explanation I feel like, but an example of what I am trying to accomplish is below. I've tried using a Max function, but to no avail. I have basic knowledge of Excel, but this might be well beyond my limitations. Any help provided would be extremely appreciated.

 Acct Main Acct Unique Result 123 11111 3 ???? 321 11111 2 ???? 443 11111 1 ???? 556 22222 1 ???? 487 33333 1 ???? 456 44444 5 ???? 789 44444 4 ???? 987 44444 3 ???? 654 44444 2 ???? 234 44444 1 ????

<tbody>
</tbody>
 Acct Main Acct Unique Result 123 11111 3 Y 321 11111 2 N 443 11111 1 N 556 22222 1 Y 487 33333 1 Y 456 44444 5 Y 789 44444 4 N 987 44444 3 N 654 44444 2 N 234 44444 1 N

<tbody>
</tbody>

Control+shift+enter, not just enter:

=INDEX(YesNoRange,MATCH(1,IF(AcctRange=Acct,IF(MainAcctRange=MainAcct,IF(UniqueRange=Unique,1))),0))

in d2, try:
Code:
``=IF(C2=MAX(IF(\$B\$2:\$B\$11=B2,\$C\$2:\$C\$11,-E999)),"Y","N")``
entered with Ctrl-Shift-Enter instead of just Enter. Copy/Paste down.

Thank you both for your input. Problem = solved!! Only if I had a tenth of y'all's knowledge in Excel, I could be dangerous.

Replies
0
Views
241
Replies
3
Views
220
Replies
5
Views
653
Replies
3
Views
117
Replies
14
Views
326

1,196,028
Messages
6,012,956
Members
441,740
Latest member
IammeResources

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