Excel 2007 : Output text based on a value in two separate columns

Hello,

I am trying to make a spreadsheet for work. Basically I'm trying to make a hold over list where at the top of the document it tells you who the next person to be frozen over is. The spreadsheet has three columns (B,C,D) and 16 rows (2-17). Column B is the person's name, column C is their rank #, and column D is the number of times they have been held over. What I need is the person with the least holdovers to be listed as the next holdover BUT I also need their rank # to be a tie breaker (lower rank gets held over first). Can someone help?

This wouldn't be necessary except that we have three supervisors and only two (max) are at work at any given time. It is difficult to communicate who is next to be held over. On top of that, we have people who volunteer to work OT and I need to count that as a holdover as a reward for volunteering. This will be the easiest way to document and reference who needs held over. Your help will be appreciated.

Thanks in advance.

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.

go to cell B1, hit F2, then hold the CTRL and SHIFT keys down, and hit ENTER.... this is an array formula

I assumed lower Rank number meant lower rank....

Try:

=INDEX($B$4:$B$19,MATCH(1,($C$4:$C$19=MAX(IF($D$4:$D$19=MIN($D$4:$D$19),$C$4:$C$19)))*($D$4:$D$19=MIN($D$4:$D$19)),0))

then hold the CTRL and SHIFT keys down, and hit ENTER