3 Ways To Make VLOOKUP Dynamic In Google Sheets (Examples)

How do you make the range and column index number in a VLOOKUP function in Google Sheets dynamic?

The VLOOKUP function is a popular formula used in spreadsheets to source data using the first column of a range as the primary key to search the search_key and then to return the intersection of the cell in that row with column_index_number .

Here is the syntax of the VLOOKUP function with its named parameters:

=VLOOKUP(search_key, range, column_index_number, [is_sorted])

As stated above the first parameter search_key is the value in the range ‘s first column you want to search, with column_index_number being the column to return from the row matching the search_key . The last optional parameter is_sorted can be set to FALSE to find an exact search for search_key , or TRUE to perform an approximate match of search_key .

(If using TRUE for is_sorted it is best to have your range sorted by the first column in ascending order to prevent getting a wrong return value.)

Knowing the parameters of the VLOOKUP formula can help to determine what aspects you want to make dynamic, and there are three parameters than can dynamically change:

• search_key this can change according to what you would want to search for;
• range this could change according to where you need to search; and
• column_index_number could change according to what value you want to be returned.

Changing the search_key value is trivial, so I’ll explore how to make the VLOOKUP function dynamic by modifying the second and third parameters in this article.

VLOOKUP Dynamic Ranges

There are multiple approaches to making the range dynamic with the VLOOKUP function. One approach is to use the INDIRECT function, which is useful when the search_key needs to be located in a different column. Another approach is to use the FILTER function, which is useful when the search_key can be found multiple times in the first column of the range .

Here are some examples demonstrating how each approach works.

Dynamic VLOOKUP With INDIRECT For range

As the second parameter in the VLOOKUP function requires a range to make this dynamic, you would need to use the INDIRECT formula .

One reason why you may want to make the range dynamic is to change the field for the primary key according to the type of search_key entered.

For example, if you have a Summary sheet containing the staff IDs, Names, Total Sales YTD and Sales for Each Month, you could make the range dynamic by using INDIRECT function according to the type of search entered for the staff member.

Here’s an example of what the Summary sheet would look like:

To construct a dynamic VLOOKUP range would mean creating a formula like so in a new sheet:

=VLOOKUP(B1,INDIRECT("Summary!"&IF(ISNUMBER(B1),"A:F","B:F")),IF(ISNUMBER(B1),4,3),FALSE)

The result is the following when entering the name Sam in cell B1 :

And here’s the same sheet where the search_key is an employee’s ID:

As you can see with both results, the desired outcome is achieved. However, when making a VLOOKUP formula dynamic when expanding or contracting the range width, you will need to make the column_index_number parameter dynamic too.

When the range of the VLOOKUP formula is modified in height, then the column_index_number can stay the same.

Dynamic VLOOKUP With FILTER For range

Another popular approach is to apply a filter on the range . This is common when the search_key can be found multiple times in the first column of the range .

As the Data sheet contains more than the Summary sheet, for example, including the individual Products sold per month if a result was needed for the total sales made of a specific product the range would need to be compressed to just those rows.

Here’s what the Data sheet looks like:

Here’s how the formula would look when using the FILTER function combined with the VLOOKUP :

=VLOOKUP(B2, FILTER(Data!A:D, Data!C:C=B3), 4, FALSE)

Here is how the sheet is constructed and it’s result:

As you can see from the above result the FILTER function helps to reduce the rows in range by the conditions placed in the second and subsequent filters. In this case, the Data sheet is reduced by column Data!C:C matching cell B3 .

VLOOKUP Dynamic Column Index Number

To make the index column in the VLOOKUP function dynamic, use the MATCH formula in the index column parameter. This approach is useful when specific data is needed from a specific column.

With the example spreadsheet used, if you wanted to obtain a specific total amount of sales for a specific month, then you can use the VLOOKUP formula with MATCH to obtain the specific intersection of the found search_key and the matched column index number.

Here’s how this formula would look:

=VLOOKUP(B2,Summary!A:F,MATCH(B3,Summary!A1:1,0),FALSE)

Here’s how the result looks in the spreadsheet:

As you can see from the above, the result changes according to the input from the specific month requested. The MATCH formula finds the matching column name in cell B3 and returns the index where this item is found, which is what is needed for the column_index_number .

Instances where it helps to make the column_index_number dynamic will happen when you want to obtain a result from the intersection of the searched row and a specific column.

Summary

There are 3 approaches you can use to make the VLOOKUP function dynamic. One approach is to modify the range using the INDIRECT formula, which can be helpful when you want to change the field for the primary key according to the type of search_key entered. When using this approach you will need to modify the column_index_number if the returning column needs to stay the same.

Another popular approach is to apply the FILTER function to the range . This is common when the search_key can be found multiple times in the first column of the range , and the data can further be reduced by applying filters.

Finally, another way to make the VLOOKUP function dynamic is by changing just the column_index_number dynamic by using the MATCH formula. This approach is useful when specific data is needed from a specific column.