Using VLOOKUP

[Madcat]

G'day guys

I'm reasonably new to Excel, I'm chasing some help regarding VLOOKUP. Amazingly I have just found the function of VLOOKUP and now I'm hooked on it, although I am quite frustrated at the same time.

I am basically trying to manufacture a positional depth chart and I'm not sure whether I should use VLOOKUP or not.

What want to know is whether I can do that above image automatically, I did the above image manually to show you what I am trying to achieve.

Any help with be terrific, thank you for taking the time to read my first post.

Cheers
Madcat

 

[Madcat]

G'day guys

I'm reasonably new to Excel, I'm chasing some help regarding VLOOKUP. Amazingly I have just found the function of VLOOKUP and now I'm hooked on it, although I am quite frustrated at the same time.

I am basically trying to manufacture a positional depth chart and I'm not sure whether I should use VLOOKUP or not.

What want to know is whether I can do that above image automatically, I did the above image manually to show you what I am trying to achieve.

Any help with be terrific, thank you for taking the time to read my first post.

Cheers
Madcat

I know I can count the number of positions using =COUNTIF(I6:I27,"*FB*") but I want to have the players that play that position listed.

Thanks

 

[Haseeb Avarakkan]

Hello, Try,

M6, copy across & down.

=IFERROR(INDEX($B$6:$B$27,SMALL(IF(ISNUMBER(SEARCH(M$5,$I$6:$I$27)),ROW($B$6:$B$27)-ROW($B$6)+1),ROWS(M$6:M6))),"")

Must hit Control+Shift+Enter, not just Enter

 

[Madcat]

Hello, Try,

M6, copy across & down.

=IFERROR(INDEX($B$6:$B$27,SMALL(IF(ISNUMBER(SEARCH(M$5,$I$6:$I$27)),ROW($B$6:$B$27)-ROW($B$6)+1),ROWS(M$6:M6))),"")

Must hit Control+Shift+Enter, not just Enter

G'day Haseeb,

Thank you so much for the reply.

I tried to use your formula in M6, copied across then down.

But I have the following:

FB              WI              CE              FE              HB              PR              HK              SR              LK 
Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd     Darius Boyd

 

[pbornemeier]

Did you use Control+Shift+Enter (instead of just Enter) to confirm the formula from post #3?

This post may be of some assistance:
http://www.mrexcel.com/archive/Formulas/4774.html

 

[Madcat]

Did you use Control+Shift+Enter (instead of just Enter) to confirm the formula from post #3?

This post may be of some assistance:
http://www.mrexcel.com/archive/Formulas/4774.html

Thanks both Phil and Haseeb. Control+Shift+Enter was the problem, I didn't use it properly.

Thanks for taking the time to help me.

Another quick question if I may, I was hoping for the positions to update automatically.

The only thing I have at the moment is the adding positions automatically to the rosters page. I have a summary page where all players stats are located, here is an example:

Adam Blair  R01  PR
Adam Blair  R02  PR
Adam Blair  R03  SR
Adam Blair  R04  SR
Adam Blair  R05  SR
Adam Blair  R06  SR
Adam Blair  R07  SR
Adam Blair  R08  SR

At the start of each season each player gets assigned a position [the position he played the most last season] and if that player starts in a different position get picks up that position automatically. Do you know of a way in Excel that we could update that automatically rather than manually entering it?

Example: Adam Blair started off the season with SR [Second Row] but he played PR [Prop] in the first game of the season. All the information would be on the Sub Total of the Summary workbook.

Name          Position(s)
Adam Blair    SR, PR

Thank you very much!