How to use dynamic range VLookup function in ms excel 2016

vlookup function

Dynamic Range Vlookup.

The VLOOKUP function is often used to find information that is stored within a table in excel. so for example, if we have a list of people’s name ages and cities and then we can in a nearby cell use the function VLOOKUP to determine Parul’s age so far this is a fairly standard and we had done this in our basic excel. Here we use the column function in column index field so that we get the result in one shot instead again type or modify the formula in next field.

But what happens if we need to add some more names to the list? The obvious thought would be to modi#
the range in the VLOOKUP. However, in a really complex model, there may be several references to VLOOKUP. This means that we would have to change each reference – assuming that we knew where they were.

However, Excel provides an alternative way – called a DYNAMIC range. ‘This is a range that expands an updates automatically. Illis is perfect if your lists are forever expanding (e.g. month on month sales data). To set up a dynamic range we need to have a range name. In the pop-up box, enter the name of our dynamic range – which is “EmpDetails”.

In the box labeled “Refers To” we need to enter the range of our data. -This is will be an achieved used by an OFFSET function. This has 5 arguments:

=OFFSET(Reference, Rows, cols, Height, Width)

  1. The Reference is the address of the TOP LEFT corner of our range – in this case, cell A3
  2. 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
  3. The Cols is the number of rows from the LEFT that we want that range to be — which will be 0 in this case
  4. The Height of the range – see below for this
  5. 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 (which is
currently 3).

Of course, we want a way of counting up the rows in our table that updates automatically — so one way of doing this
is to use the COUNTA function. This just counts up the number of non-blank cells in a range. As our names are in column A, the number of entries in our data is COUNTA(A: A). Note that if you were to put this in a cell you would get the value 4 – as it includes the header Names. However, that it is immaterial.

So in the “Refers To” box we put:

=OFFSET(sheet1! A3,,0,0,counta(A:A),3)

And click the OK button. Our dynamic range is now created. Now return to the VLOOKUP formulae and
replace the range with the name of our new dynamic range EmpDetails so we have:

So far nothing has changed. However, if we add a few more names to our table. In the cell where we had Parul,
replace it with a new name such as Deepak (that wasn’t on the original list):

We see that Excel has automatically returned Deepak’s age & City – even though we haven’t changed the VLOOKUP formulae. Instead, the scope of the dynamic range 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
will automatically update in the data validation list.
Instead of Offset function for the dynamic range, you may also do this by using excel table function. see the below figure for dynamic Vlookup and data validation using a table:

Here we put array formula to compute Vlookup. we will discuss the array Formula in details in another post.

Dynamic range is very useful when we have increased data- especially when VLOOKUP and PIVOT table is required.






Subhendu Bera
I am Subehndu Bera From India I love to write on a web. always looking for something new to learn. interested in technology and science, and right now I will be pursuing a Bcom in Kolkata.

How to do index match multiple criteria in excel

Previous article

How to Sumproduct function work in different condition

Next article

You may also like


Leave a reply

Your email address will not be published. Required fields are marked *

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