A lookup column allows options to be chosen from a drop-down list to be chosen rather than by entering the data manually. The data that is stored in this drop-down list is saved within the overall database within its own table. So, before creating a lookup column, create the table that will store the contents of the drop down list.
This table can be created using the Access table wizard. Normally, it would only have two columns

1. Double Click create table by using wizard
2. Select a table form close to the one required and then select individual fields in order to customise to the task required
3. When fields have been selected and renamed (if needed) click Next
4. Change the name of the table to an appropriate vale. Click Next.
5. Do not create any relationships at this stage, just click Next.
6. Click Next to enter directly into table and click Finish

1. Open the main table and click on the header of the column before which the lookup column design is to be inserted. Click on Insert on the task bar and click on Lookup Column.
2. Leave the default of lookup column to look up values and click Next.
3. Choose the table from which the column is to draw its data. Click Next
4. Select fields to be displayed during lookup and click Next
5. The width of the column can then be adjusted. Click Next
6. Give the field a label and click Finish

Having updated the main table it is necessary to reflect these changes in the Excel workbook used for analysis.

1. Export the data from the created table used by the lookup column in the main table.
2. Create a new worksheet in the Excel workbook and copy the exported data into it.
3. In both the worksheet for the exported main table data and the Merged worksheets insert a column in the same place as in the main table. That is the inserted column should appear before the same column in both worksheets as in the Access table. This will also cause Excel to automatically change the =LOOKUP statements in the merged worksheet to point to the shifted columns.
4. Export the data from the main table. Delete the entries in the existing main table worksheet (ctrl-a to select all data and then delete). Copy the exported records into the main table worksheet.
5. In the merged worksheet add an appropriate =LOOKUP statement.
6. Refresh the PivotTable