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

 

[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 an
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 colum
if its not none display the cell, it was a really long string but hey i
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 unneccassary

The formula works equally for

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

 

[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.