Dynamic Index Column in Vlookup in Google Sheets

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:

Example: Dynamic Index Column in Vlookup

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.

example to a basic Vlookup formula in Google Sheets

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]

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.