Explanation

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. The workbook exists in the same directory and the data in the file looks like this:

Sample product data in an external workbook - 1

Note the data itself is in the range B5:E13.

VLOOKUP formula

The formula used to solve the problem in C5, copied down, is:

=VLOOKUP(B5,'[product data.xlsx]Sheet1'!$B$5:$E$13,4,0)

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 “t able_array " argument. The syntax for external references is:

'[workbook]sheet'!range
  • workbook - the name of the external workbook (product data.xlsx)
  • sheet - the name of the sheet containing the range (Sheet1)
  • range - the actual range for the 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 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 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 the 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

Explanation

In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell.

When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR function to trap this error. However, when the result in a lookup table is an empty cell , no error is thrown, VLOOKUP simply returns a zero.

This can cause problems when the lookup table contains actual zero values, because it suggests that blank cells in the lookup table also contain zeros, when they in fact are empty. To work around this problem you can test the result of VLOOKUP explicitly with the IF function , then return a custom result if you find an empty string.

With IF function

To test the result of VLOOKUP directly, we use the IF function like this:

=IF(VLOOKUP(E5,data,2,0)="",""

Translated: if the result from VLOOKUP is an empty string (”"), return an empty string.

If the result from VLOOKUP is not an empty string, run VLOOKUP again and return a normal result:

VLOOKUP(E5,data,2,0)

In both cases, the fourth argument for VLOOKUP is set to zero to force an exact match.

Note: you can use the same general approach with the XLOOKUP function .

With LEN or ISNUMBER

Depending on your needs, you can expand the idea above to run more specific tests. For example, to test for cells that literally have zero characters (i.e. a length of zero), you can use the LEN function like this:

=IF(LEN(VLOOKUP(E5,data,2,0))=0,"",VLOOKUP(E5,data,2,0))

To test for numeric results only, you can use the ISNUMBER function , and reorder the logic like this:

=IF(ISNUMBER(VLOOKUP(E5,data,2,0)),VLOOKUP(E5,data,2,0),"")

Translated: if the result from VLOOKUP is a number, return a normal lookup. If not, return an empty string ("").