Vlookup function

[Kaz]

I am trying to finsih of a pageant scoring spreadsheet and have run into
this issue.

I have 3 judges columns and their cumulative scores add up in a 4th
column. From there, I have to rank the scores, which I do successfully
in the far left column. Then I use the Vlookup function below the rows
and columns to easily identify the winner so that the judges and Emcee
can have quick results right before them. My on snafu is it will not
properly identify any ties, just the first contestant to have a #1
ranking.

I've attached the file and have used this kind of formula:
=VLOOKUP(1,J22:K33,2,FALSE)
at each of the category winners as well as the final tally winners.

What am I missing or can this even be done.

The Tie Score example is highlighted in RED.

Kaz


+----------------------------------------------------------------+
| Attachment filename: snow queen scoring.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=362318|
+----------------------------------------------------------------+

 

[Dan E]

Kaz,

What do you want to happen when there is a tie? Should it tell you that
there is a tie and leave it at that. Or, do you want it to tell you the 2 people
who have tied?

Dan E

 

[Kaz]

Just need it to tell me or flag the 2 tied contestants in a differen
color.

Thanks

Ka

 

[Dan E]

Kaz,

I'll use your first section as an example:

Change your VLOOKUPS to something like:

=IF(COUNTIF(A5:A16,1)=1,VLOOKUP(1,A5:C16,2,FALSE),"TIE")

Which will check if their is more than one 1 and if so put in "TIE" instead
of the person in first.

You could add conditional formatting to flag the winners in colors so that
if more than one exists you'll be able to pick them out more easily

Select the range B5:B16 (Contestants)
Format -> Conditional Formatting
Change the dropdown to "Formula Is" and enter "=A5=1" (without quotes)
Set a format
Click Add
Repeat the Above process for "=A5=2" and "=A5=3" if you also want to
highlight the 2nd and 3rd places.
Click OK

Dan E

 

[Kaz]

Works great. Thanks for the help