US Trends

how to do vlookup in excel with two spreadsheets

To do VLOOKUP in Excel with two spreadsheets, you use the same VLOOKUP formula, but point the table_array to the other sheet or workbook.

What VLOOKUP Does

VLOOKUP lets you pull matching data from Spreadsheet B into Spreadsheet A using a common ID (like Product ID, Email, or Student Number).

Basic syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value : what you’re searching for (e.g., an ID in your current sheet).
  • table_array : the range in the other sheet/file that contains the ID and the data you want to bring back.
  • col_index_num : column number (within that range) to return.
  • [range_lookup] : FALSE for exact match (most common), TRUE for approximate.

Example: Two Sheets in the Same Workbook

Imagine:

  • Sheet1: “Orders” – has Product ID in column A; you want Product Name.
  • Sheet2: “Products” – Product ID in column A, Product Name in column B.

On Sheet1 , cell B2:

  1. Click B2.

  2. Type:
    =VLOOKUP(

  3. Click A2 (your Product ID). Formula becomes:
    =VLOOKUP(A2,

  4. Go to Sheet2 , select the table (A:B, or A2:B100 etc.). Formula becomes:
    =VLOOKUP(A2,Sheet2!$A$2:$B$100,

  5. Type the column index to return. Product Name is in column 2 of that range:
    =VLOOKUP(A2,Sheet2!$A$2:$B$100,2,

  6. Use FALSE for exact match and close bracket:
    =VLOOKUP(A2,Sheet2!$A$2:$B$100,2,FALSE)

Press Enter, then drag the formula down.

HTML version of this formula example

html

<table>
  <tr>
    <th>Sheet</th>
    <th>Cell</th>
    <th>Formula / Value</th>
    <th>Meaning</th>
  </tr>
  <tr>
    <td>Sheet1 (Orders)</td>
    <td>A2</td>
    <td>Product ID</td>
    <td>Lookup value used by VLOOKUP</td>
  </tr>
  <tr>
    <td>Sheet2 (Products)</td>
    <td>$A$2:$B$100</td>
    <td>ID and Name columns</td>
    <td>Range VLOOKUP searches in other sheet</td>
  </tr>
  <tr>
    <td>Sheet1 (Orders)</td>
    <td>B2</td>
    <td>=VLOOKUP(A2,Sheet2!$A$2:$B$100,2,FALSE)</td>
    <td>Returns Product Name for the ID in A2</td>
  </tr>
</table>

Example: Two Different Workbooks (Two Files)

Say you have:

  • File 1: Users.xlsx – where you want to pull info.
  • File 2: DataSet.xlsx – where the reference table lives.

Steps (both files open):

  1. In Users.xlsx , choose the cell where you want the result (e.g., B2).

  2. Type:
    =VLOOKUP(

  3. Click the cell containing the lookup value (e.g., A2).

  4. Type a comma and switch to DataSet.xlsx.

  5. Select the full table range there (e.g., A2:F101).

    • Excel automatically writes something like:
      =VLOOKUP(A2,[DataSet.xlsx]SheetName!$A$2:$F$101,

  6. Type the column index number (e.g., 4 to pull from the 4th column in that range).

  1. Type FALSE (exact match) and close the bracket:

=VLOOKUP(A2,[DataSet.xlsx]SheetName!$A$2:$F$101,4,FALSE)

Press Enter, then fill down.

HTML version of cross-workbook pattern

html

<table>
  <tr>
    <th>Part</th>
    <th>Example</th>
    <th>Description</th>
  </tr>
  <tr>
    <td>lookup_value</td>
    <td>A2</td>
    <td>Cell with the ID you want to match</td>
  </tr>
  <tr>
    <td>table_array</td>
    <td>[DataSet.xlsx]SheetName!$A$2:$F$101</td>
    <td>Range in other file containing IDs and data</td>
  </tr>
  <tr>
    <td>col_index_num</td>
    <td>4</td>
    <td>Column in that range you want to return</td>
  </tr>
  <tr>
    <td>range_lookup</td>
    <td>FALSE</td>
    <td>Exact match only</td>
  </tr>
  <tr>
    <td>Full formula</td>
    <td>=VLOOKUP(A2,[DataSet.xlsx]SheetName!$A$2:$F$101,4,FALSE)</td>
    <td>Pulls value from other workbook</td>
  </tr>
</table>

Online / URL-Based Workbooks (Excel Online)

When workbooks are in the cloud (e.g., OneDrive), the table_array can look like a long URL, but the idea is the same:

=VLOOKUP(
A2,
'[https://d.docs.live.net/spreadsheet- id/Docs/[dataset.xlsx]dataset'!$A$2:$F$50](https://d.docs.live.net/spreadsheet- id/Docs/%5Bdataset.xlsx%5Ddataset'!$A$2:$F$50),
4,
FALSE
) This tells Excel to look in the online workbook’s sheet and range, then return the 4th column value for the ID in A2.

Common Mistakes (and Fixes)

  • #N/A error
    • ID doesn’t exist in the other spreadsheet, spaces or different text, or TRUE instead of FALSE for range_lookup.
  • Wrong column returned
    • col_index_num counted from the left of your selected range , not the sheet, so check you’re using the right column index.
  • Formula breaks when inserting columns
    • Use narrower ranges or structured tables, or consider alternatives like XLOOKUP or combining columns with array literals in some tools.

One-Line “Recipe” You Can Reuse

  • Same file, other sheet:
    =VLOOKUP(A2,OtherSheet!$A$2:$D$100,3,FALSE)
  • Other file:
    =VLOOKUP(A2,[OtherFile.xlsx]SheetName!$A$2:$D$100,3,FALSE)

Information gathered from public forums or data available on the internet and portrayed here.