AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
![]() Begin entering the VLOOKUP function normally. The easiest way to enter a reference to an external range is to use the "point and click" method. ![]() This allows the formula to be copied down the column without the range changing. Also, note the range is entered as an absolute reference. 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. range - the actual range for the table array ($B$5:$E$13). ![]() sheet - the name of the sheet containing the range (Sheet1).workbook - the name of the external workbook (product data.xlsx).The syntax for external references is: 'sheet'!range The only difference is the special syntax used for external references, in the "t able_array" argument. range_lookup is zero to force an exact match.col_index is 4, to retrieve data from column 4. ![]() table_array is a reference to a range in an external workbook.This is a standard use of the VLOOKUP function to retrieve data from the 4th column in a table: The formula used to solve the problem in C5, copied down, is: =VLOOKUP(B5,'Sheet1'!$B$5:$E$13,4,0) Note the data itself is in the range B5:E13. The workbook exists in the same directory and the data in the file looks like this: In this example, the goal is to use VLOOKUP to find and retrieve price information for a given product stored in an external Excel workbook. ![]()
0 Comments
Read More
Leave a Reply. |