Use Excel's Lookup functions to search a database

You can use Excel’s Lookup functions to build a worksheet that can be used to search a database table. For example, say you’ve imported the following table from your Access database to Sheet2 of your Excel workbook:

To build a worksheet that can be used to look up an intern’s pay rate by entering the intern’s ID, follow these steps:

1. Open the workbook, click the Sheet2 tab, and select the range A2:H5.
2. Click in the Name box, type Interndata, and then press [Enter].
3. Click on the Sheet1 tab.
4. Click cell D6 and enter Employee ID.
5. Click cell D8 and enter Name.
6. Click cell E8 and enter the following function:

=VLOOKUP(E6,Interndata,3,FALSE)&" "&VLOOKUP(E6,Interndata,2,FALSE)

7. Click in D10 and enter Pay Rate.
8. Click in E10 and enter the following function:

=VLOOKUP(E6,Interndata,8,FALSE)

9. Change the cell format of E6, E8, and E10 to match the data type of the data in the table.
10. Add a header and formatting as shown here.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.