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


me+excel=crazy Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=486165