Vlookup Question?

[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!

[Dave Peterson]

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

(one cell)



gillemi wrote:
>
> 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!
>
> --
> gillemi
> ------------------------------------------------------------------------
> gillemi's Profile: http://www.excelforum.com/member.php...o&userid=33694
> View this thread: http://www.excelforum.com/showthread...hreadid=535506

--

Dave Peterson

[gillemi]

Thank you!

[gillemi]

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

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

Thanks again!

[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


gillemi wrote:
>
> If the golfer score entries are entered in C2, C7, C12 etc., and the
> names are B2, B7, B12 etc., How would you approach a formula for this
> late out?
>
> I don't think the C2:C50 will work because I have other unrelated
> numbers in between C2 & C7 etc.
>
> Thanks again!
>
> --
> gillemi
> ------------------------------------------------------------------------
> gillemi's Profile: http://www.excelforum.com/member.php...o&userid=33694
> View this thread: http://www.excelforum.com/showthread...hreadid=535506

--

Dave Peterson

[gillemi]

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

How should I tell the function to ignore blank values?

Thanks!