Help with index array

Hi guys,

I am looking for a formula to return the team from each tab based on the name value.

I have tried using index but i cant seem to get it to work,

please can anyone help me with this one?

thanks

EDIT:: =INDEX(Team1:Team3!1:1048576,Team1:Team3!2:2,MATCH(Sheet4!B2,Team1:Team3!A:A))

This is the forumla i am trying to use, but it just returns VALUE

=IF(COUNTIF(Team1!$A$4:$A$31,B2)>0,Team1!$A$2,"")&IF(COUNTIF(Team2!$A$4:$A$31,B2)>0,Team2!$A$2,"")&iF(COUNTIF(Team3!$A$4:$A$31,B2)>0,Team3!$A$2,"")

Try this one

Not pretty. But in A2 enter
=IF(NOT(ISERROR(VLOOKUP(B2,Team1!$A:$A,1,0))),"Team 1",IF(NOT(ISERROR(VLOOKUP(B2,Team2!$A:$A,1,0))),"Team 2",IF(NOT(ISERROR(VLOOKUP(B2,Team3!$A:$A,1,0))),"Team 3","Not Found")))

and drag it down

You can also try this:

=IFERROR(VLOOKUP(B2,Team1!$A:$A,1,0),IFERROR(VLOOKUP(B2,Team2!$A:$A,1,0),VLOOKUP(B2,Team3!$A:$A,1,0)))

@Alkey
the result of your formula is the name of the player and not the name of the team.

I am looking for a formula to return the team from each tab based on the name value.

Thanks popipipo. It looks like I misread the requirements. Sorry

This is what it should have been:

=RIGHT(IFERROR(VLOOKUP(B2,Team1!$A:$A,1,0)&"TEAM 1",IFERROR(VLOOKUP(B2,Team2!$A:$A,1,0)&"TEAM 2",VLOOKUP(B2,Team3!$A:$A,1,0)&"TEAM 3")),6)