Help with index array

[dxuberz]

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

[popipipo]

Please Login or Register  to view this content.

Try this one

[JieJenn]

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

[AlKey]

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)))

[dxuberz]

Hey guys, thanks for all the solutions! i went with popipipo's if you were interested!

Just a pain to do it across 10 sheets and was hoping there was a fun workaround!

:D

Anyway got it done in the end thanks

[popipipo]

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

[AlKey]

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

[AlKey]

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)