Google Sheets Vlookup Dynamic Range

featured image of google sheets vlookup dynamic range

VLOOKUP is a powerful function in Google Sheets for finding data in large tables. It is widely used by businesses and individuals to simplify data management and analysis tasks. The dynamic VLOOKUP is an advanced version of the standard VLOOKUP, offering even more versatility and flexibility to users. In this article, we will explore the benefits of using Google Sheets dynamic range VLOOKUP and learn how to implement it in your next project.

overview image of vlookup dynamic range in google sheets


Problem with Regular VLOOKUP Function in Google Sheets and Why is Having a Dynamic Range Necessary?

The regular VLOOKUP function in Google Sheets has two main limitations: it can only search for values in the first column of the lookup table, and it cannot automatically adjust the lookup range when data is added or deleted from the table.

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

vlookup image syntax

  • Search key,” the first argument, defines the value to look for in the first column of the given range.
  • range“, the second argument, defines the table or range of cells to search within.
  • The third argument, “index“, indicates the column number within the range to return the matching value.
  • The final and fourth argument is “is_sorted” which determines whether the first column of the range is sorted in ascending order. If the first column is sorted, set “is_sorted” to TRUE, otherwise set it to FALSE.

Check the following images for example.

simple vlookup in google sheets

We have a database of expenses made by employees and we want to determine the amount spent by Amanda. A simple VLOOKUP can easily retrieve this information, but the date of the expenses is not included in the dataset. Adding a date column could alter the results of the VLOOKUP.

problems in simple vlookup

Having a dynamic range with VLOOKUP overcomes these limitations, as it allows for searching in any column and automatically adjusts the lookup range. This results in more flexible and efficient data lookups.

example of dynamic vlookup in google sheets

As an example, we used the same dataset. But in this instance, including a new date column has no impact on the outcome. Instead, it changed the range for columns E to F. Observing the formula makes it simple to see the shift.

benefit of using dynamic vlookup in google sheets

Therefore, it is clear that VLOOKUP dynamic range is quite beneficial in google sheets when used professionally.


2 Simple Approaches to Apply Dynamic Range in VLOOKUP in Google Sheets

Dynamic range in VLOOKUP is a valuable feature in Google Sheets that helps ensure the accuracy and efficiency of your data analysis. With dynamic range, the VLOOKUP formula automatically adjusts to match the size of the data range.

This article will explore two simple approaches to applying dynamic range in VLOOKUP in Google Sheets.


1. Implementing VLOOKUP Field Values as Array in Same Worksheet

Assume that we have a financial database that showcases the employee expenses incurred during their business trips.

I. For Range Field

Consider that we want to determine the type of expense and department by using the employee name as the lookup value and using the dynamic VLOOKUP feature.

dataset for vlookup range field

Steps:

  • Firstly, we will select cell I6.

selecting cell in google sheets

  • Secondly, we will use the following formula to find the expense type by the employee.
=VLOOKUP(H6,{B:B,E:E,D:D},2,0)

using formula for vlookup dynamic range field

  • The curly braces {} in the VLOOKUP formula allow you to select multiple columns in any order, unlike the normal VLOOKUP using parentheses () which follows a sequential order.
  • For example, in the formula, columns B, E, and D are selected using curly braces in an order that can benefit us.
  • The difference between using curly braces and parentheses in VLOOKUP can impact the results of the calculation, so it’s important to understand when to use each.
  • Finally, press ENTER to see the result.

final output of google sheet vlookup dynamic range field

  • The formula will return the value in the second column (column index 2) of the matching row that contains the lookup value in column E.

We can also get the department just by changing the column index from 2 to 3.

=VLOOKUP(I5,{B:B,E:E,D:D},3,0)

multiple output as example for range field

By adjusting the index number, we can specify which column in the matching row to return the value from. For instance, we used the same formula to retrieve the department name.

Read More: How to Use VLOOKUP with Named Range in Google Sheets


II. For Index Field (Getting Multiple Results with VLOOKUP Dynamically)

We will use the same dataset, but this time we will retrieve all the relevant information in one go. For instance, we will search by the employee name and retrieve the type of expense, the amount of the expense, and the name of the department.

data for index field

Steps:

  • Once again we will select cell I6 first.
  • Now, we will use the following formula.
=ARRAYFORMULA(VLOOKUP(H6,$B$6:$F$14,{4,5,3},0))

vlookup formula for dynamic index field

  • Finally, pressing the ENTER key will display all the relevant results.

application of arrayfromula with google sheet vlookup dynamic index field

Formula Breakdown:

  • The first argument for the VLOOKUP function is “H6” as a search key.
  • Second argument for the VLOOKUP function is “$B$6:$F$14“, which refers to the data range being searched.
  • The third argument is “{4,5,3}“, which tells the formula to return the values in columns 4, 2, and 5 for the matching row.
  • The final argument for the VLOOKUP function is “0“, which means that an exact match must be found in the data range for the formula to return a result.
  • Finally, ARRAYFORMULA enables the formula to return a result over a range of cells.

The use of ARRAYFORMULA in dynamic VLOOKUP has the benefit of allowing you to return multiple results from multiple columns at once. This saves time and effort compared to having to use multiple individual VLOOKUP formulas.

dynamic vlookup projection

Read More: How to VLOOKUP Multiple Columns in Google Sheets (3 Ways)


2. Using Functions to Create Dynamic Range from Different Worksheets

We will use two new datasets. One is about supplies and their quantities and costs and another is about toy store products. These data are in a different sheet or spreadsheet. The following methods will help us understand how to access it.

data in a seperate sheet in google sheets

Consider that we will use the product supply data in the Datasheet to illustrate these methods.

I. Using INDIRECT Function for VLOOKUP Range

The INDIRECT function combined with VLOOKUP allows for dynamic referencing of data, making it easy to update VLOOKUP formulas as data changes. This saves time and effort by eliminating the need to manually adjust formulas every time the source data changes.

Syntax:

INDIRECT(cell_reference_as_string, [is_A1_notation])

Assume that we’ll use the INDIRECT function to find the total cost of products supplied by Suppliers C, E, and B. The data is on a separate sheet called “Data” and will be used as a cell reference.

Steps:

  • At first, we will select cell C6.
  • Then, we will apply the following formula.
=VLOOKUP(B8,INDIRECT("'"&B$4&"'!"&"B4:F13"),5)

using indirect function with vlookup

Formula Breakdown:

  • The VLOOKUP function searches for a value (B8) in the first column of a specified range table array.
  • Generally, the INDIRECT function creates a dynamic cell reference to a specific sheet and range, in this case, the “Data” sheet and B4:F13 range.
  • The 5 in the VLOOKUP formula indicates which column from the specified range will be returned as a result.
  • The formula returns the value from the fifth column of the specified range where the value in B8 is found in the first column.
  • Finally, press ENTER to see the total cost of the product supplied by Supplier C.

using indirect function with google sheet vlookup for dynamic range

  • The formula allows us to search for data outside the current sheet. It helps to determine the total cost for Suppliers E and B with ease.

final output of using indirect function

It is evident that instead of manually copying data from one sheet to another, we can simply use the INDIRECT and VLOOKUP formulas to reference the data and automatically retrieve it.

Read More: Combine VLOOKUP and HLOOKUP Functions in Google Sheets


Similar Article


II. Using FILTER Function

Using the VLOOKUP function, we can not only search for data but also filter it by combining it with the FILTER function. Let’s take an example where we want to find the total cost of Product Q supplied by Supplier C.

Syntax:

FILTER(range, condition1, [condition2, ...])

dataset of supply products in a different sheet

The FILTER function allows us to easily find specific products from Supplier C, which supplies both Product Q and Product Z.

Steps:

  • Firstly, We will select cell C6.

selecting cell for using filter function

  • Secondly, we will use the following formula to filter data.
=VLOOKUP(C5,FILTER(Data!B:F,Data!C:C=E5),5,0)

using filter function with vlookup

  • In the end, press ENTER to see the total cost.

result from google sheet vlookup with filter function for dynamic range

Formula Breakdown:

  • C5 is the lookup value to be searched for in the data range.
  • FILTER(Data!B:F,Data!C:C=E5) returns a filtered data range from the “Data” sheet, where the values in column C match the value in cell E5.
  • 5 is the column number of the data range to return the result.
  • 0 indicates an exact match is required for the lookup value.
  • The above image shows us that we used the FILTER function along with VLOOKUP to filter data and find the total cost of Product Q supplied by Supplier C, which is $2000.

The combination of FILTER and VLOOKUP allows us to quickly and efficiently find specific information from large data sets.

final output of using filter and vlookup for dynamic range

By using this combination, we provided multiple examples such as, we find the total cost for Product Y supplied by Supplier B, and can easily extend the search for other suppliers and products.

Read More: Alternative to Use VLOOKUP Function in Google Sheets


III. Using MATCH Function for Dynamic Column Number

The use of the MATCH function as a column index in the VLOOKUP dynamic range offers two benefits: improved accuracy and flexibility in data retrieval.

Syntax:

MATCH(search_key, range, [search_type])

It allows us to select the exact column we want to retrieve data from, eliminating the need for manual updates to our formulas when the structure of our data changes.

dataset for supply products in google sheets

Assume that we are going to find the cost per unit for the product supplied by supplier C.

Steps:

  • Once again, we will select cell E5.

how to select cell

  • Now, we will use the following formula
=VLOOKUP(C5,Data!B:F,MATCH(D5,Data!$B$3:$3,0),0)

formulation of vlookup with match function

  • Finally, press ENTER to see the result.

result from using match function with vlookup

Formula Breakdown:

  • Firstly, the VLOOKUP formula takes the value in cell E5 as the lookup value.
  • Secondly, it references the data range in the “Data” sheet from column B to column F.
  • Next, it uses the MATCH function to find the column index where the header value in cell D5 is found in the range Data!$B$3:$3.
  • Finally, the VLOOKUP formula returns the value in the same row as the lookup value and in the column found by the MATCH function, with an exact match (0) setting.

The output is $20 which means that the cost per unit for the product supplied by Supplier C is $20.

multiple output for using match with vlookup

By combining the power of FILTER and VLOOKUP, we are able to effortlessly uncover valuable information from our data. The formula helps us find the cost per unit for a specific supplier, like Supplier E which is $18.

Read More: How to VLOOKUP Last Match in Google Sheets (5 Simple Ways)


IV. Using IMPORTRANGE Function

The IMPORTRANGE function in Google Sheets allows us to easily retrieve data from another spreadsheet.

Syntax:

IMPORTRANGE(spreadsheet_url, range_string)

In this example, we have a dataset of a toy store’s products and we want to find out how many are in stock using the product ID. The link to the dataset is as follows.

https://docs.google.com/spreadsheets/d/1elXrfifeRObk2-uBDN7x0rifBsSwmNAHoqyGgBQFuR0/edit#gid=0

dataset in a different spreadsheet

Steps:

  • First, we will select cell E5.
  • Second, we’ll input the formula:
=VLOOKUP(C5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1elXrfifeRObk2-uBDN7x0rifBsSwmNAHoqyGgBQFuR0","Data!B5:F14"),5,0)

formulation of importrange and vlookup

  • C5 is the search key for the product ID we want to look up.
  • IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1elXrfifeRObk2-uBDN7x0rifBsSwmNAHoqyGgBQFuR0″,”Data!B5:F14”) retrieves the data from another spreadsheet.
  • 5 is the index number, representing the column we want to retrieve the data.
  • 0 specifies an exact match, rather than an approximate match.
  • After inputting the formula, press ENTER and then, the cell will show the stock count for the specified product ID.

result from using importrange function with vlookup

And with that, we have successfully used the VLOOKUP and IMPORTRANGE functions to find the stock count of our toy store products.

multiple examples of usingn importrange function with vlookup

Read More: VLOOKUP with IMPORTRANGE Function in Google Sheets


Conclusion

The use of dynamic range in the Google Sheets VLOOKUP function allows for a flexible and efficient way to search for specific data within a large dataset. It eliminates the need for manual updates of ranges, ensuring accuracy and saving time in the process. The dynamic range can be applied in different ways, such as using the INDEX and MATCH functions or the ARRAYFORMULA function, depending on the specific requirement. Check OfficeWheel for more relevant content.


Related Articles

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo