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

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

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

To return the player name that corresponds to the high sco

=INDEX(name_range,MATCH(MAX(score_range),score_ran ge,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.

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

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

=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:

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

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

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" wrote in message
...
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

Vlookup Question - Reffering to a column other than the leftmo
 

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" wrote:

To return the player name that corresponds to the high sco

=INDEX(name_range,MATCH(MAX(score_range),score_ran ge,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.

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

Vlookup Question - Reffering to a column other than the leftmo
 

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.


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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" wrote:

To return the player name that corresponds to the high sco

=INDEX(name_range,MATCH(MAX(score_range),score_ran ge,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.

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

Vlookup Question - Reffering to a column other than the leftmo
 

50
50
Those (could) become:
49.9999999998
49.9999999997

A more accurate description would be:

Those will be evaluated as.....


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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" wrote:

To return the player name that corresponds to the high sco

=INDEX(name_range,MATCH(MAX(score_range),score_ran ge,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.

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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