Here is a simple trick that you can use to get dynamic index column in Vlookup. In Google Sheets, there are two methods that you can follow to get a dynamic Index Column in Vlookup. What are they?
1. You can create a virtual array (Vlookup range) and then freeze the index column number. Here the index column number is constant. The index column number would be always # 2. You can delete any column or insert new columns. But it remains 2 and brings dynamism to the index column number.
2. Use the Columns function as the Index number. It adjusts the Vlookup index column when you delete or add new columns in the range.
Suppose in 5 column data set, you may want to return a value from column 3 based on a lookup in Column A. Column 3 is your index column here.
What happens when you insert a new column between the Colum 1 and 2 or 2 and 3? Your index column number is still 3 in the Vlookup. It should be 4.
You can freeze the third column (not the column number 3) in Vlookup irrespective of its column position or index number. Let me explain you this concept called dynamic Index Column in Vlookup in Google Sheets.
Tips to Get Dynamic Index Column in Vlookup in Google Sheets
Sample Data:
In this, I want to lookup a name in column A to get that person’s date_of_arrival which is in column C.
In Vlookup, the index number of the date_of_arrival column is 3. How to freeze this index column (not column number) in Google Sheets?
First, see one normal Vlookup formula that returns the date_of_arrival based on lookup name “Patrick”
=vlookup("Patrick",A2:E7,3,FALSE)
This formula will look for the name “Patrick” in Column A, in the range A2: E7, and return the value from column 3 in the corresponding row.
In this formula, the column index is not dynamic. That means if you insert a new column after A or B, the index number should be 4. Because the position of the date_of_arrival column is now 4, not 3. But in the formula, it remains 3.
As said earlier, you can freeze the index column, again not index column number, in 2 ways in Google Sheets.
Dynamic Index Column Formulas
Formula 1 to Get Dynamic Index Column in Vlookup in Google Sheets
=vlookup("Patrick",A2:G7,columns(A2:C7),FALSE)
Formula 2 to Get Dynamic Index Column in Vlookup in Google Sheets
=vlookup("Patrick",{A2:A7,C2:E7},2,FALSE)
The first formula makes the index number dynamic by using the Columns function. The column function counts the column from A to C and returns the number three. This number three is our column Index number in Vlookup.
When you insert or add new rows between A to C, the column index number gets changed dynamically.
The second formula follows a different approach to create dynamic Index Column in Vlookup.
In this formula, the column index number should be always 2 irrespective of your actual column Index. Because in this formula the lookup range only includes the first column that is the lookup column and then the range starting from the index column.
For example, our original data range is A2: E7. But in Vlookup, I’ve modified this range as below skipping the columns between Column A and C.
{A2:A7,C2:E7}
So there wouldn’t be any effect of inserting or deleting new columns.
Conclusion:
The above are the two easiest solutions to make the index column dynamic in Vlookup in Google Sheets.
Of course, you can also use Index-Match as an alternative. But Vlookup is the best and easiest one to follow.
Related Reading:
1. How to Use Vlookup Across Different Sheet Tabs in Google Sheets
2. Multiple Values Using Vlookup in Google Sheets is Possible [How to]
3. Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup]
