Lookup and metadata columns are probably my absolute favorites because they allow me to enter data in one place, and then reuse it in other places. You use the lookup column type when you want to take data from another list and use it in the current list. One place where I have used it, is in the Missions, Goals and Outcomes project for our department. I had 3 lists: Goals, Objectives and Tactics. Each of them uses a lookup column to refer to the one above it. So, Objectives includes a Goal column which is a lookup from the Goals list. Tactics has two lookup columns: one for Objectives, and one for Goals. There are other ways that we could have created this setup, but lookup seems to work quite nicely.
To learn more about this column type, let’s see what Microsoft has to tell us about it:
Use this column field type to let people choose values based on information that is already stored in the site. For example, if you want a column to store the names of customer accounts to which employees are assigned and the list of accounts must be limited to a Customer Accounts list on the site, you can create a Lookup column that displays the names in the Customer Accounts list. The list of choices in a Lookup column appear in either an expanding box, called a drop-down menu, or a list box, depending on whether you allow people to select more than one value.
You can customize a Lookup column in these ways:
Choose the source of the lookup values Specify which list, library, or discussion board contains the values that you want to store in the column. The source cannot be a subsite, workspace site, wiki, or blog. After you specify the list, library, or discussion board that you want, you can specify which column in that list, library, or discussion board contains the values that you want people to choose from.
Allow multiple selections Allow people to choose as many values as they like or limit the number of values that they can choose to only one value. If people can choose multiple values, all of the values appear in the column, separated by a semi-colon (;).**
Choose columns to display You can add one or more columns to show specific field values for this column type.**
** I wish they would tell you more about these two features.
Just a quick tip with Allowing Multiple Selections, is if you allow people to choose more than one option, your ability to filter (and sometimes sort) will go away for that column. Sorting and filtering can only happen on single choice options.
Adding more columns to show can be a super useful feature, because you can then use that data in calculations elsewhere. For example, in the Community Site discussion board app, I wish Microsoft had included the Category ID as an additional column when it connected the Category name to the discussion list. Given our propensity for changing department names, it would have been helpful for us to have a Category ID number by which to filter our custom view. I am in the process of researching a workaround for that so we will not have to change our customized views each time a department name changes. Stay tuned!