The single quotes (’) in the formula above are necessary because “product data.xlsx” contains a space character.
Note the data itself is in the range B5:E13.
VLOOKUP formula
The formula used to solve the problem in C5, copied down, is: This is a standard use of the VLOOKUP function to retrieve data from the 4th column in a table:
lookup_value comes from B5 table_array is a reference to a range in an external workbook col_index is 4, to retrieve data from column 4 range_lookup is zero to force an exact match
The only difference is the special syntax used for external references, in the “able_array"argument. The syntax for external references is:
workbook is the name of the external workbook (product data.xlsx) sheet is the name of the sheet containing the range (Sheet1) range is the actual range for table array ($B$5:$E$13)
Note the workbook and sheet part of the reference are enclosed in single quotes (’) because the file name “product data.xlsx” contains a space character. Also note the range is entered as an absolute reference. This allows the formula to be copied down the column without the range changing.
Entering the reference
The easiest way to enter a reference to an external range is to use the “point and click” method. Begin entering the VLOOKUP function normally. Then, when entering the table_array argument, browse to the the external workbook and select the range directly in the other file. Excel will construct the needed reference automatically. Note the reference will change depending on whether the external file is open or not. If the external workbook is open, VLOOKUP will show the workbook name and address for the table_array argument, as in the the screenshot above. If the external file is not open, VLOOKUP will display the full file path to the workbook + workbook name and address.
Handling spaces and punctuation
Note the reference to workbook is enclosed in square brackets, and the entire workbook + sheet is enclosed in single quotes. The single quotes (’) are required when the workbook or sheet name contains space or punctuation characters
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
