VLOOKUP with RANK and IF

[mull21]

Hi

I'm not sure what code should i need to use.
In sample file tab "Teams" are teams ranked. Now on tab "1" (and later on next tabs same thing) i need Team name taken from "Team" tab accourding team rank position. So that tab 1 will take rank1 team, tab 2 will take rank2 team and so on. Also need to take team persons name and HCP according that.

Can anyone tell me what kind of code i should use... i believe it is possible to to with VLOOKUP and IF ?

[Estevaoba]

I guess we can do that with the OFFSET function.
Try this in C1:

Please Login or Register  to view this content.

The first MATCH argument VALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)) returns the current sheet name, i.e., 1. So when you make a copy of this sheet and rename it 2, for example, it will return 2, and so on.

For the names, try this in C3 and copy down:

Please Login or Register  to view this content.

For the HCP, this in D19:

Please Login or Register  to view this content.

Good luck!

[mull21]

super... that worked like i needed it. Thank you, thank you!

[Estevaoba]

You're welcome. Glad to help.
Thank you for the rep points and for marking the thread SOLVED.