Vlookup Question?

G

gillemi

I have the names of golfers in B2:B50. I have their scores in C2:C50.
In C52 I am using the "Min" Formula to return the lowest score for the
week.

In C53 I would like to return the the name that coincides with the
lowest score. If there is a tie, I would like it to return the word
"Roll Over".

Thank you!
 
D

Dave Peterson

=IF(COUNTIF(C2:C50,MIN(C2:C50))>1,"Roll Over",
INDEX(B2:B50,MATCH(MIN(C2:C50),C2:C50,0)))

(one cell)
 
G

gillemi

If the golfer score entries are entered in C2, C7, C12 etc., and th
names are B2, B7, B12 etc., How would you approach a formula for thi
late out?

I don't think the C2:C50 will work because I have other unrelate
numbers in between C2 & C7 etc.

Thanks again
 
D

Dave Peterson

First, I would lay out my data in a more tabular form.

But if that's not possible, I'd use another column to indicate which row
contained the data. I put an X in column D to indicate that this was a "score"
row.

Then I used this array formula:

=IF(SUMPRODUCT(--(D2:D22="x"),--(C2:C22=MIN(IF(D2:D22="x",C2:C22))))>1,
"Roll Over",
INDEX(B2:B22,MATCH(1,(D2:D22="x")*(C2:C22=MIN(IF(D2:D22="x",C2:C22))),0)))

(all one cell)

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
G

gillemi

I used the array formula that you suggsted and it works, except whe
there are blank values? The formula is reading the blank value as th
"Min".

How should I tell the function to ignore blank values?

Thanks
 
Top