To do a VLOOKUP in Excel with two spreadsheets, use a formula that references the second spreadsheet's data range by naming the sheet and range within the VLOOKUP function. The basic formula syntax is:
=VLOOKUP(lookup_value,SheetName!TableRange,column_index,FALSE)\text{=VLOOKUP}(\text{lookup\_value},\text{SheetName!TableRange},\text{column\_index},\text{FALSE})=VLOOKUP(lookup_value,SheetName!TableRange,column_index,FALSE)
For example, if you want to look up a value from Sheet1 in Sheet2, you write:
=VLOOKUP(A2,Sheet2!$A$2:$C$10,3,FALSE)
Explanation:
- lookup_value : The value you want to find (e.g., A2 in Sheet1).
- SheetName!TableRange : The location of the lookup table in the other sheet (e.g., Sheet2!$A$2:$C$10). Use absolute references ($) to fix the range when dragging the formula.
- column_index : The column number in the table range that has the data you want to return (e.g., 3 means return from the third column).
- FALSE : Indicates you want an exact match.
If your sheet name contains spaces or special characters, enclose it in single quotes like this:
=VLOOKUP(A2,'Sheet2'!$A$2:$C$10,3,FALSE)
You can also do VLOOKUP between two different workbooks by using the workbook name and sheet name in the formula:
=VLOOKUP(A2,[WorkbookName.xlsx]Sheet1!$A$2:$C$10,3,FALSE)
After entering the formula in a cell (e.g., Sheet1 cell E2), press Enter and drag the fill handle down to apply the function to other rows. This approach lets you fetch data from a second spreadsheet into the first using VLOOKUP.
