Index Function with named range and [ ]

[RicardoCubed]

Any help in educating me would be much appreciated. How do I structure/create the functionality to be able to refer to the column name using [ ]. See below. The use of the brackets to contain the column header is what I would like to be able to replicate in other worksheets. I don't think the column header are included in the named range sf, yet the formula looks to the column name. Help please. Thanks!

Excel 2010
	A	B	C	D
29	USS Hood	=INDEX(sf[Ship name],8)
30
31	Ship name	Class	Registry	Captain
32	USS Constitution	Constitution	NCC-1700	Rory Hohstadt
33	USS Defiant	Constitution	NCC-1764	Lursa
34	ISS Enterprise	Constitution	NCC-1701	Anil Maro
35	USS Enterprise	Constitution	NCC-1701	Reka Mavok
36	USS Excalibur	Constitution	NCC-1664[1]	Hayden McLaren
37	USS Exeter	Constitution	NCC-1672[1]	Crani th'Shari
38	USS Farragut	Constitution	NCC-1647[1]	T'Sai
39	USS Hood	Constitution	NCC-1703[1]	Britteny Esperon
40	USS Intrepid	Constitution	NCC-1631[1]	Tonita Toten
41	USS Lexington	Constitution	NCC-1709[1]	Thiria ch'Vraasia
42	USS Potemkin	Constitution	NCC-1657[1]	T'Hain
43	USS Yorktown	Constitution	NCC-1717.	Lincoln Avison
44	USS Pegasus	Constitution	NCC-1702.	Matha Vangelos
45	USS Defiant	Defiant	NCC-75633	Nasian Gora
IndexMatchConcate

Cell Formulas
Range	Formula
A29	=INDEX(sf[Ship name],8)

 

[ChrisM]

Using brackets is a feature of Tables (with a capital T). To create a table, prepare your data as above, click any cell inside your data so Excel knows where you are, then go to ribbon Insert, then Table, then click the "has headers" option.

 

[RicardoCubed]

Thanks for the help!