If you use Vlookup, then you must know about the use of the dynamic column in Vlookup Importrange combination in Google Sheets.
The main reason, you can Vlookup an external Google Sheets file with the help of the Google Sheets function Importrange.
In such use, the Importrange output acts as the Vlookup ‘range’. Vlookup accepts this combination without any error.
VLOOKUP(search_key, range, index, [is_sorted])Dynamic column, what’s that?
In Vlookup you need to provide a column number to return a value or values. This number is called “index”.
Assume you have a two column product price list, in which column A contains the product’s names and B contain its price.
If you want the price of any product by searching in column A, use must use column 2 as the index column in Vlookup.
If the product list is in D1
It’s advisable to make this column number dynamic. I mean auto adjust the column number when you insert new columns in your data range. Here is one example.
The Normal Vertical Lookup (Vlookup) Formula in Cell D2 (using static index column number):
=vlookup("Banana",A1:B,2,false)The Dynamic Column Vertical Lookup (Vlookup) Formula in Cell D3 (using dynamic index column number):
=vlookup("Banana",A1:B,column(B1),false)I have earlier explained this in my tutorial – Dynamic Index Column in Vlookup in Google Sheets.
Also, before going to read further, I suggest you read my tutorial – How to Vlookup Importrange in Google Sheets [Formula Examples]. That post contains some awesome tips.
Why a Dynamic Index Column in Vlookup Importrange is a Must
Suppose you have used the Importrange data, i.e., data in an external Google Sheets file, as the range in Vlookup. The above issue of index column may surfaces here also.
There are more chances of forgetting to update the index number in Vlookup when you make changes in the source (external) file.
So to make a set and forget Vlookup + Importrange, this dynamic index column approach is a must.
How to Refer to a Dynamic Column in Vlookup Importrange in Google Sheets
I have two options right now. Pick the one that you feel convenient for you to follow. See that under the two different subtitles below.
I am going to do my level best to make things easier for you to follow.
Sample data to explain the use of the dynamic index column concept in Vlookup Importrange formula in Google Sheets:
Named Ranges as Vlookup Index Column in Google Sheets (Option 1)
See the step by step instructions below.
Settings in the Source File (FILE_A)
Step # 1:
The above sample data is in the source file (call it FILE_A) “Sheet1”. In that file, add a new tab “Sheet2”.
Later I will import the data in “Sheet1” to another Google Sheets file (call it FILE_B) and Vlookup the price in column B in the source.
That means column 2 is our index column. I am going to first make the column number dynamic.
Step # 2:
In FILE_A I have already inserted a new tab “Sheet2”. In cell A1 in that tab, enter the following formula.
=column(Sheet1!B1)This is a helper tab and helper cell. So we don’t want any other rows or columns in that Sheet.
If you want, you can delete all the other rows and columns in the “Sheet2” tab. Always delete unwanted rows and columns in Google Sheets to enhance Sheets performance.
Step # 3:
Select the cell that contains the above Column formula. Then go to the menu DATA > NAMED RANGES. Name the cell as “price”.
Settings in the Output File (FILE_B)
Time to use the Importrange in FILE_B.
Step # 1:
In cell A1 in FILE_B enter the URL of the FILE_A. Then in cell A2 enter the source data range Sheet1!A1:1000 not, Sheet1!A1
Finally in cell A3 type the Named Range name, i.e., “price”.
If you have any doubt, please check the below screenshot.
Step # 2:
In cell B4, enter your search key (product name).
Step # 3:
Now in this step, let me share you how to get a dynamic column in Vlookup Importrange formula in Google Sheets.
Formula:
=vlookup(B4,importrange(A1,A2),importrange(A1,A3),false)
As usual, if you see #REF! Error, that means you have not given ‘Allow Access’ to Importrange.
In that case, first, use any of the Importrange formulas independently and give ‘Allow Access’. You can then delete that formula and use it in the above combo formula.
To test the output, go to FILE_A and insert a new column between A and B. Come back to FILE_B and see the Vlookup Importrange formula automatically adjusts the index column number.
Dynamic Index Column in Importrange + Vlookup Array Formula
We can use the above formula to get the price of multiple products at one go! See the Array Formula in cell C4, which populates an array result.
=ArrayFormula(vlookup(B4:B6,importrange(A1,A2),importrange(A1,A3),false))
Match formula as Vlookup Index Column in Google Sheets (Option 2)
This is another approach to input dynamic column in Vlookup Importrange formula. This is based on the
Here prepare your FILE_B as follows. I have given useful tips on the screenshot itself.
Here is the non-array Vlookup + Match + Importrange Formula. In this the Match formula returns the dynamic column number, I mean the column number that moves/changes.
=vlookup(B4,importrange(A1,A2),match(A4,importrange(A1,A3),0),false)In array (multiple search keys) usage, the formula will be as follows. Considering the search keys are in the range B4:B6.
=ArrayFormula(vlookup(B4:B6,importrange(A1,A2),match(A4,importrange(A1,A3),0),false))Here again, I have used the Match formula to act as the dynamic column ID in Vlookup + Importrange.
How?
The Match formula uses the field label of the Index column as the search key and returns its position in the range. When that column moves, accordingly the Match will return the correct column number.
That’s all. Any doubt, you can ask me in the comments. Enjoy!
Additional Resources:
