vlookup issue ( not working and im tearing my hair out)

[me+excel=crazy]

Okay , well i cant discuss the actual info in my sheet but ill come up with a good analogy of it all . Ok basically on sheet 2 i have all the teams in my NHL ( my company) listed under the immediate manager. I also have the players last name, first name and number set out as such ;

Team A
Shivers Paul 14
Jacobsen John 25
Tucker Tanya 98

Team B
Pitt Brad 22
Murray Bill 28
Clinton Hillary 55

You get the point, ok , on sheet 1 ( my main page accessible to my company ) , i have this format...

Managers last name Players last name




Player first name Players last name Players number


When you type in the managers name and players last name the other three should auto populate. So first thing i did was set up a formula to allow the page to search for players first name as this;
=IF(managers name=team a,VLOOKUP(players last name,list of players,2,FALSE),D1)
this states that whatever name is put in the managers name it will search the list of that team for the last name and populate the first name.
this is set up for each team, oh and by the way d1 is just set to popoulate the field as NONE. I have changed the cell names to better explain this. That formual works great , no issues there. I have this setup on page 2 at the end of all the teams.

Second thing was extreamly easy players last name on page 1, just had it equal the type in field above it.

Players number wont be hard i can do a Vlookup for it as well to search the team page similar to how i searched for the palyers first name.

Now here in lies my issue, after all my teams are set up on page 2 , with all the corresponding =if lines to calculate the players first name are set up i end up with a list generally looking like this ( when searching for let say manager team b and Murray)

None
Bill
None
None

and so on ......my issue is , and im sure its just somthing simple im not doing, but how do i do a lookup in that colum for any field not equaling the word none. I have tried everything, and have even went through the help database. If i have to alter my setup im ok with that, just please help .

THANK YOU IN ADVANCE

Jason

[Bryan Hessey]

Given what you are trying to do, I would use a helper column for the lookup, being after the manager and player names, insert a combined column =A1&B1 and look up where (say) the manager name is in G1, and the player is in H1, and sheet2 has Column A=team, B=player surname, C=A1&B1 formula copied down), and D-E-F-etc contain player details, as

=VLookup(G1&H1,Sheet2!C1:Sheet2!E99,2,False)

Hope this helps

Okay , well i cant discuss the actual info in my sheet but ill come up with a good analogy of it all . Ok basically on sheet 2 i have all the teams in my NHL ( my company) listed under the immediate manager. I also have the players last name, first name and number set out as such ;

Team A
Shivers Paul 14
Jacobsen John 25
Tucker Tanya 98

Team B
Pitt Brad 22
Murray Bill 28
Clinton Hillary 55

You get the point, ok , on sheet 1 ( my main page accessible to my company ) , i have this format...

Managers last name Players last name




Player first name Players last name Players number


When you type in the managers name and players last name the other three should auto populate. So first thing i did was set up a formula to allow the page to search for players first name as this;
=IF(managers name=team a,VLOOKUP(players last name,list of players,2,FALSE),D1)
this states that whatever name is put in the managers name it will search the list of that team for the last name and populate the first name.
this is set up for each team, oh and by the way d1 is just set to popoulate the field as NONE. I have changed the cell names to better explain this. That formual works great , no issues there. I have this setup on page 2 at the end of all the teams.

Second thing was extreamly easy players last name on page 1, just had it equal the type in field above it.

Players number wont be hard i can do a Vlookup for it as well to search the team page similar to how i searched for the palyers first name.

Now here in lies my issue, after all my teams are set up on page 2 , with all the corresponding =if lines to calculate the players first name are set up i end up with a list generally looking like this ( when searching for let say manager team b and Murray)

None
Bill
None
None

and so on ......my issue is , and im sure its just somthing simple im not doing, but how do i do a lookup in that colum for any field not equaling the word none. I have tried everything, and have even went through the help database. If i have to alter my setup im ok with that, just please help .

THANK YOU IN ADVANCE

Jason

[damorrison]

I like this example
contextures.on.ca/excelfiles.html
ScenarioAlt.xls
it uses INDEX/MATCH FORMULA,

when you look through the list in the web page you will find many other
ideas

[me+excel=crazy]

I actually solved it anopther way staying with my current setup and formulas , basically for anyone that has this issue , what i did was, on the column of names that shows up :
a
1 NONE
2 NONE
3 NONE
4 BILL
5 NONE
6 NONE

I made a if string that said if the first is NONE go to the next column if its not none display the cell, it was a really long string but hey it works. Was sorta like this.

=if (a1=none,if(a2=none,if(a3=none,NONE,a3),a2),a1)

It works great..

[Bryan Hessey]

The formula as shown doesn't work, the 'none' needs to be in quotes, however the test and use of A3 remains unnecessary

The formula works equally for

=IF(A1="none",IF(A2="none",A3,A2),A1)

I actually solved it anopther way staying with my current setup and formulas , basically for anyone that has this issue , what i did was, on the column of names that shows up :
a
1 NONE
2 NONE
3 NONE
4 BILL
5 NONE
6 NONE

I made a if string that said if the first is NONE go to the next column if its not none display the cell, it was a really long string but hey it works. Was sorta like this.

=if (a1=none,if(a2=none,if(a3=none,NONE,a3),a2),a1)

It works great..

[damorrison]

what happens if more than cell doesn't equal "none"

[Bryan Hessey]

The data is taken from the first encountered cell within the range that does not equal 'none'

More to the point is that, based on the original data, and then on the second shown data, 'Bill' will appear as a player for the first team.

The first data showed a blank row between teams, in which case the formula will show the desired results.
The second data shows no detectable break between teams and the formula does not test for same-team-person.


The OP's explanation is a little vague when said that "made a if string that said if the first is NONE go to the next column" but appears to mean the next row.

Those points could just be in the presented test data, and although not a method that would be recommended the item may work for the OP's requirement.

what happens if more than cell doesn't equal "none"