# How do i reference another tab in my spreadsheet

#### lumpar

##### New Member
Hello

I am trying to create a lookup or if statement, i am a bit clueless when it comes to this so apologies.

I have tab 1 of my spreadsheet and in a1 i would like to reference some data on another page my other page would be players, and in the players i would have 4 sections, for example goalkeepers, defenders etc

example is trevor
 Player Club Value Points Trevor Badajoz 1.0 52

<tbody>
</tbody>

I would type in trevor in a1 in my first tab and it would then reference the players list and fill in club, value, points, the reference would need to look at goalkeepers, defenders etc.

Can anyone help with this please?

Thanks

Matt

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Oeldere

##### Well-known Member
this is how it can be done.

notice the differance between B6 en B7 (2 different formula's)

you can also work with defined names.

sheet1
A
B
C
D
5
Player
Club
Value
Points
6
Trevor
1.0
52
7
van Basten
NL
15
53

<TBODY>
</TBODY>

Worksheet Formulas
Cell
Formula
B6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,2,0)
C6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,3,0)
D6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,4,0)
B7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)
C7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)
D7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)

<TBODY>
</TBODY>

<TBODY>
</TBODY>

#### lumpar

##### New Member
I tried doing this and cannot get to work

I cannot get it to reference it just has an N/A error

Any ideas?

Last edited:

#### Oeldere

##### Well-known Member
1) Then VLookup had no match.

2) Are the namens in sheet 2 sorted A- Z?

If not VLookup has problem with that.

You can use then index / match.

#### TheCman81

##### Well-known Member
When using Vlookup you don't have to sort A-Z

I look at this and post back

Also can you post the name of your tabs? Also what does the data in your second tab look like. Could do with you posting some of it?

Last edited:

#### lumpar

##### New Member
When using Vlookup you don't have to sort A-Z

I look at this and post back

Also can you post the name of your tabs? Also what does the data in your second tab look like. Could do with you posting some of it?

hi

The tabs are called teams and players, they are based on team name in order. So i have goalkeeper
arsenal, everton etc, then defenders they will be based on the same

eg
GK 4 W Szczesny Arsenal £4.5m
GK 65 S Given Aston Villa £1.5m
GK 24 P Cech Chelsea £5.5m

 DF 463 K Gibbs Arsenal £3.5m DF 461 J Djourou Arsenal £3.0m DF 197 S Squillaci Arsenal £3.0m DF 169 A Santos Arsenal £2.5m DF 167 P Mertesacker Arsenal £3.5m DF 106 B Sagna Arsenal £4.0m DF 105 T Vermaelen Arsenal £6.0m DF 103 L Koscielny Arsenal £4.0m DF 329 R Vlaar Aston Villa £2.5m DF 236 R Dunne Aston Villa £3.0m DF 186 M Lowton Aston Villa £1.5m DF 136 S Warnock Aston Villa £2.0m DF 115 C Clark Aston Villa £1.5m

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Would really appreciate any guidance

#### lumpar

##### New Member
Hi Guys any suggestions, i'm really stuck. would appreciate any assistance

#### Jim885

##### Well-known Member
Because you have named your worksheets "teams" and "players", all you need to do is substitute the appropriate worksheet name for "sheet2" in the below formula.

Worksheet Formulas
Cell
Formula
B6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,2,0)
C6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,3,0)
D6
=VLOOKUP(\$A6,sheet2!\$A\$2:\$D\$3,4,0)
B7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)
C7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)
D7
=VLOOKUP(\$A7,sheet2!\$A\$2:\$D\$3,COLUMN(),0)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

#### Jim885

##### Well-known Member
I assume that your second worksheet (tab) is named, players. In this case, you would enter players! in place of sheet2!

Don't forget to use the ! mark after players. The rest of the formula remains the same as previously posted.

#### lumpar

##### New Member
thanks Jim i will try this.

Replies
1
Views
456
Replies
28
Views
1K
Replies
7
Views
5K
Replies
1
Views
739
Replies
1
Views
457

1,196,017
Messages
6,012,867
Members
441,737
Latest member
bijayche

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back