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.