VLOOKUP Not Picking up Table Array in Another Spreadsheet

The VLOOKUP function is one of those functions we use in Microsoft Excel on a regular basis to find information on a large spreadsheet. But like any other function, it has its limitations and applications. So it is common to encounter errors while using the function. In this tutorial, we will discuss fixings and workarounds for the VLOOKUP function not picking up a table array in another spreadsheet.

VLOOKUP Not Picking Up Table Array in Another Spreadsheet: 7 Fixes

There could be many reasons the VLOOKUP function is not picking up a table array in another Excel spreadsheet. The #NA, #VALUE, and #REF errors can occur from any formation error of the function’s arguments. Although the major issue that causes the problem is that more than one instance of the file might be open on the system. Also, there are some other issues involved too. Either way, we have discussed all of the problems that may cause the problem and how you can fix them below.

Solution 1: Close One if Separate Instances are Open

This is the main reason why the VLOOKUP function in our spreadsheet is not picking up any table array from another spreadsheet. Although updates from Excel 2016 have lessened the issue and made it somewhat usable. But still, there are reports of the same instance causing problems for the VLOOKUP function and causing the function to not pick up table arrays.

Some of the signs that your worksheet is open in another instance are-

Formula references in another open workbook aren’t working.
Copying and pasting from one to the other has fewer options.
Also, you can check from the Excel ribbon. You need to open the Switch Windows option from the Windows group of the View Here, you will find a drop-down with the names of all the workbooks and all the instances that are open.

So the solution to the problem is pretty simple-

You just have to close the separate instances of the workbook that are open at the same time in the first place.
Keep in mind that if you haven’t manually opened the workbook separately, it may still happen. So make sure to check for the signs above and close the extra workbooks of the same file to make the function pick up from table arrays in another spreadsheet again.

Solution 2: Check for Correct External References

Another reason that the VLOOKUP function is not picking up the table array in another spreadsheet is the reference for the table array was incorrect in the first place. This is very common, especially for people typing out references manually.

For example, take a look at the following table.

It is in the first spreadsheet. The table starts from cell B4 and ends in cell D13.
If we try to use the VLOOKUP function in the second spreadsheet, and we use a wrong reference like the following, an error is bound to happen.

Upon pressing Enter the result will look like this.

This is because we are looking for the exact match in the wrong reference here. If you use an approximate match instead, the answer would still not be as expected.

So, make sure to watch out for the correct references. This is true for not only the cell ranges but also for the sheet names.

Solution 3: Inspect the File Path for References from Another Workbook

Yet another way to mess up the references is using the wrong file path. As we mentioned earlier, using wrong references sometimes results in an error or an unexpected result.

Usually, the method to link the formula to an external workbook is to use the file name before the sheet name of where you are taking the reference from, followed by an exclamation mark. Another way to redirect the link is to use use the file name before that. It is something like this:

='/users/user/Google Drive/testfolder/source.xlsx'!cell_name

But if you mess up anywhere in the path (the one inside the inverted comma), it will search for the wrong direction. This will result in not finding the proper data and thus the VLOOKUP will not be picking up table array from the spreadsheet of another workbook.
An easy way to use the correct formula is to go to the file explorer and double-click the file path on top of the explorer.

Then select the path name, copy it, and paste it while typing the file reference. This way, we can avoid any error that improper writing of the file path may cause.

Solution 4: Look if the Search Mode is Correct

The fourth argument of the VLOOKUP function is the range_lookup. This is an optional argument that takes a boolean input. It searches for the approximate match if we put a TRUE value in it. Otherwise, it searches for the exact match with a FALSE argument if we initiate it.

Exact match, as the name suggests, searches for the exact match for the function. Meanwhile, the approximate match will look for the first closest match for the criteria. So if you do not have the correct search mode as an argument Excel will look for other matches that you might not intend. For example, you want an exact match, but because you entered 1 or TRUE as an argument, Excel will look for the first close match and return the wrong result.

Usually, you put this value in the fourth argument, range_lookup.

Solution 5: Sort LOOKUP Column

This is a concern if you are looking for an approximate match. What an approximate match does is it takes the first match from the selected range/array. When you do not have the data sorted within the dataset, the VLOOKUP function will not pick up the correct value from the table array in another spreadsheet, or even the same one for that matter.

You can sort data manually by selecting and moving cells. If you have the dataset as a table on your spreadsheet, you will have a downward-facing arrow button in the column headers. Click on them and you will find some sorting options there.

If you want more flexibility, you can find the Custom Sort option through the drop-down menu of the Sort & Filter in the Editing group of the Home tab.

Solution 6: Check for Duplicates

The VLOOKUP function always looks for the first match no matter the match type. In the case of multiple entries with the same value, the function ignores all of them except the first. So make sure you don’t have multiple entries with the same argument you are searching for.

Now for a demonstration, let’s go back to the dataset again. Let’s assume we need the following value with the VLOOKUP.

If we apply the VLOOKUP formula in another spreadsheet now from this table array, we will see it is not picking up the correct value from this table array.

This is because there were two “Furniture” in the table array.

Excel is picking up the first one. Thus giving us an incorrect result.

So always check for duplicates in your dataset if the VLOOKUP function is not picking up the correct data from a table array from either the same or different worksheet.

Solution 7: Look for the Proper Return Column

The third argument in the VLOOKUP function is the col_index_num as Excel suggests. This is the column index number or the return column. Any output from the VLOOKUP function is from this column. The function first searches for the match of the first argument in the second array’s left column. When it matches once, it returns the value from the col_index_num position right on the same row.

For example, we want the price value from our table array and we put the function like this.

It clearly doesn’t show an accurate result. It is returning the date value in Excel’s format, as we have entered 2 as the return index. And the second column of the table was containing date values.
If we enter 3 (as the third column contains the price list) as the col_index_num, it will now provide us with a more accurate result.

So be sure to check for a proper return index. Otherwise, the VLOOKUP will not return the correct value even though it will be picking up the table array either in the same or another worksheet.

Things to Remember

It is always a good idea to lock the table array while fetching it within the same workbook.
Another good practice is to remove formulas containing VLOOKUP functions that fetch data from another workbook. As the file paths or directory change, the formula will not function properly in those cases.
Remember to close different instances of Excel workbooks if they are open before working with the formula.
Check for proper arguments and their positions while inserting them into the formula.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

VLOOKUP Not Picking Up Table Array.xlsx

Conclusion

So these were all about the solutions for the VLOOKUP function not picking up a table array in another spreadsheet in Excel. Hopefully, one of the solutions worked for you and the function is working properly for your spreadsheets. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!