How to Use VLookup With Different Sheets on Excel
Excel's time-saving "vlookup" function lets you substitute a code in place of longer text, which takes time to type. "Vlookup" uses a table called a "lookup table" that links the abbreviated code with its full description. The lookup table does not have to be on the same sheet as the table that uses codes from the lookup table. In fact, keeping the lookup table on its own sheet prevents you from confusing the lookup table from your true tables.
Type the following data into "Sheet1" of an Excel workbook, pressing "Tab" in place of the commas. This table data associates a job code with a description for that code. This lookup table lets tables on any other sheet in the workbook use a code in place of typing the full description.D, PainterE, SculptorF, Designer
Click the top left cell of the lookup table just entered, then drag to the bottom right cell. This action selects the table. Type the name "jobs" in the text box to the left of Excel's "Formula bar," which sits directly above the worksheet grid. The text box into which you typed is the "Range name" box.
Click cell "C4" of any sheet in your workbook besides "Sheet," then type the following sample table. This table uses a job code, for which the "vlookup" function will display descriptive text.Name, Job codeClark Kent, dLois Lane, e
Type the text "Job description" in the cell to the right of the "Job code" header. Click the cell below the "Job description" header and type this function: "vlookup (D5, jobs, 2)". Notice that Excel displays the job description where you typed the "vlookup" function. That function's first argument is a cell reference for the cell containing the first job code of your sample table from step 3. The second argument refers to the lookup table. The third argument is the index of the job description column in the lookup table.
Click the lower right corner of the cell you just typed, then drag down to the last row in your sample table. This fills the "Job description" column with the job descriptions that the "vlookup" function provides. Note that these descriptions are on a different sheet from that of the sample table.