Dynamic Range Vlookup.
The VLOOKUP function is often used to find information that is stored in excel within a table. So, for example, if we have a list of people’s names ages and cities and then we can use the VLOOKUP function in a nearby cell to determine Parul’s age so far this is a fairly standard and we did this in our basic excellence. Here we use the column function in the column index field to get the result in one shot instead of type again or modify the formula in the next field.
But what if we need to add a few more names to the list? The obvious thought would be to modify the range in the VLOOKUP. However, there may be several references to VLOOKUP in a really complex model. This means we’d have to change every reference-assuming we knew where they were.
Excel, however, provides an alternative way-called a DYNAMIC range. This is a range that automatically expands an update. Illis is perfect if your lists are constantly expanding (e.g. monthly sales data). We need a name for a range to set up a dynamic range. Enter the name of our dynamic range in the pop-up box-which is “EmpDetails.”
In the box labeled “Refers To” we need to enter the range of our data. -This is will be achieved used by an OFFSET function. This has 5 arguments:
=OFFSET(Reference, Rows, cols, Height, Width)
- The Reference is the address of the TOP LEFT corner of our range – in this case, cell A3
- The Rows is the number of rows from the TOP LEFT that we want that range to be — which will be 0 in this case
- The Cols is the number of rows from the LEFT that we want that range to be — which will be 0 in this case
- The Height of the range – see below for this
- The Width of the range – this is 3 as we have TWO columns in our range (the person’s name, age and their
Now the height of the range will have to vary depending on the number of entries in our table (currently 3).
Of course, we want a way to count the rows in our table that automatically updates — so one way to do this is to use the COUNTA function. This only counts the number of non-blank cells in a range. Since our names are in column A, the number of entries in our data is COUNTA(A: A). Note that if you put this in a cell, you’d get the value 4-as it includes the header names. It’s immaterial, though.
So in the “Refers To” box we put:
And click on the OK button. Our dynamic range is now being created. Now return to the VLOOKUP formulae and replace the range with the name of our new EmpDetails dynamic range so we have:
Nothing has changed so far. If we add a few more names to our table, though. In the cell where we had Parul, replace it with a new name like Deepak (not on the original list):
We see that Excel has automatically returned the age & city of Deepak – although we have not changed the formulae of VLOOKUP. Instead, the dynamic range scope has increased to include the extra names.
Here we have also created a dynamic range for Name field so that when we select the name field in lookup table it
Automatically update the data validation list. Instead of the dynamic range offset function, this can also be done by using the Excel table function. See the dynamics figure below Vlookup and data validation using a table:
Here we put the formula array to calculate Vlookup. We will discuss the Formula array in detail in another post. Dynamic range is very useful when we have increased data- especially when VLOOKUP and PIVOT table are required.