Looking for information in different worksheet using vlookup/hlookup?

[VGPOP]

I am a baseball fanatic and I keep track of baseball player's stats in each worksheet. I have a total of 30 worksheets (each one named for each baseball team, ex: Arizona, Atlanta, Baltimore, Texas, Washington, etc)

I am using another worksheet so I can compare two players for example. I am using a drop-down list. I select NYMets and all the Mets players show up in one cell on the drop-down list. I want to be able when I select a specific player, all his stats to show up (I already keep track in each worksheet). For example:

I have a nice table with each player's info and it goes like this: (I am making this number up)

AB12 | AC12 | AD12 | AE12 | AF12 | AG12 | AH12 |
JOSE REYES | GAMES | BA | R | H | RBI | SB |
AB13: OVERALL | 100 | 0.345 | 100 | 189 | 80 | 50 |
AB14: LAST 7 | 7 | 0.311 | 11 | 15 | 20 | 9 |
AB15: LAST 3 | 3 | 0.655 | 3 | 5 | 9 | 4 |

In the above example, AB12 has the value of "JOSE REYES", AC12 value of "GAMES", AD12 value of "BA", etc, etc.

In the NEW worksheet, I want to select a drop-down list "NYMets" and it brings me all the players, when I select JOSE REYES, I want all his stats to show up. If anyone can show me how to be able to accomplish this I can finish the rest.

I tried using vlookup function, but it could only go down one row, for example:

A1 = NYMets
A8 = JOSE REYES (on the drop-down list)

From AB12 to BB119 I keep ALL my information for all players

VLOOKUP($A$8, indirect($A$1&"!AB12:BB119"), 2, FALSE)

In the above example, it's giving me GAMES because I chose "2" in the col_index_num. But what if I want to get the value "0.655" which is in AD15 in the NYMets sheet?

I hope I am not too confusing.

I'd appreciate any inputs.

Thanks.

 

[cameron.richens]

the number "2" refers to the number of columns or rows in your lookup... so if you put a "3" (BA) it will be the next column a "4" (R) the column after that...

for a V or H lookup to work you require the correct information in the far vertical or horisontal "left" or "top"... otherwise

Vlookup = useless

if your data was as below you could use a vlookup.

AB12 | AC12 | AD12 | AE12 | AF12 | AG12 | AH12 |
Player | GAMES | BA | R | H | RBI | SB |
AB13: JOSE REYES| 100 | 0.345 | 100 | 189 | 80 | 50 |
AB14: Babe Ruth | 7 | 0.311 | 11 | 15 | 20 | 9 |
AB15: Big Hitter | 3 | 0.655 | 3 | 5 | 9 | 4 |

 

[VGPOP]

the number "2" refers to the number of columns or rows in your lookup... so if you put a "3" (BA) it will be the next column a "4" (R) the column after that...

I know that. It's just that I don't want those values to show up.

For example, if I use a vlookup function for OVERALL field, how will it know that I want the "OVERALL" stats from JOSE REYES, and not other Mets player?

Every single player has those fields.

 

[cameron.richens]

also a Vlookup only displays the first occurance of the data that it finds... so doubly usless for your requirement without formatting your data differently.

 

[VGPOP]

also a Vlookup only displays the first occurance of the data that it finds... so doubly usless for your requirement without formatting your data differently.

Thanks for the info!