Formula for Returning Highest Value in a Set

[wilsork]

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</SPAN>	Main Acct</SPAN>	Unique</SPAN>	Result</SPAN>
123</SPAN>	11111</SPAN>	3</SPAN>	????</SPAN>
321</SPAN>	11111</SPAN>	2</SPAN>	????</SPAN>
443</SPAN>	11111</SPAN>	1</SPAN>	????</SPAN>
556</SPAN>	22222</SPAN>	1</SPAN>	????</SPAN>
487</SPAN>	33333</SPAN>	1</SPAN>	????</SPAN>
456</SPAN>	44444</SPAN>	5</SPAN>	????</SPAN>
789</SPAN>	44444</SPAN>	4</SPAN>	????</SPAN>
987</SPAN>	44444</SPAN>	3</SPAN>	????</SPAN>
654</SPAN>	44444</SPAN>	2</SPAN>	????</SPAN>
234</SPAN>	44444</SPAN>	1</SPAN>	????</SPAN>

<TBODY>
</TBODY>

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

<TBODY>
</TBODY>

 

[Aladin Akyurek]

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

 

[Part Time user]

in d2, try:

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

 

[wilsork]

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.