Vlookup Question - Reffering to a column other than the leftmost?

[mpenkala]

Hi there,

I have a table full of stats for a basketball team. It's laid out something
like so:
Player Name - Posistion - Pts/Game - Rebounds/Game - Asst/Game - High Score

Lower down on the same page I have a spot for the High games of the year.
I've used the LARGE function to find out the top 3 scores. Now I'm trying to
use VLOOKUP to match the score given by LARGE,1 and give the name of the
player who scored it.

I've used VLOOKUPS before but is it possible to lookup a number in the table
that isn't the leftmost column?

Thanks,
Matt

 

[T. Valko]

To return the player name that corresponds to the high score:

=INDEX(name_range,MATCH(MAX(score_range),score_range,0))

If you want the top n:

=INDEX(name_range,MATCH(LARGE(score_range,ROWS(A$1:A1)),score_range,0))

Copy down as needed.

However, (seems there's always a however !!!) this will not work properly if
there are duplicate high scores that fall within the top n.

 

[Marcelo]

=OFFSET(C29;MATCH(D29;$C$29:$C$35;0)-1;-1;1;1)

adjust the ranges as your needs

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"mpenkala" escreveu:

 

[Peo Sjoblom]

Use INDEX and MATCH

=INDEX(B2:B50,MATCH(MAX(A2:A50),A2:A50,0))


=INDEX(B2:B50,MATCH(LARGE(A2:A50,2),A2:A50,0))


and so on



--


Regards,


Peo Sjoblom

 

[mpenkala]

Hey T. Valko,

thanks, this seems to work good. I think I'll just add a decimal place to
fix the "same score" problem. (Change a score to 10.1 and the other to 10.0,
then format to show no decimals)


Cheers,
Matt

 

[T. Valko]

I think I'll just add a decimal place to fix

the "same score" problem

That's the "trick" we use, only we do it within the formula. That way your
numbers stay the same if you use them in other calculations. It does make
the formula more complicated and now it becomes an array formula** :

=INDEX(name_range,MATCH(LARGE(score_range-ROW(score_range)/10^10,ROWS(A$1:A1)),score_range-ROW(score_range)/10^10,0))

We're essentially subtracting a very small decimal value from each score to
"break" any ties.

50
50

Those (could) become:

49.9999999998
49.9999999997

So, now there is no tie.

 

[T. Valko]

50

50
Those (could) become:
49.9999999998
49.9999999997

A more accurate description would be:

Those will be evaluated as.....