Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data.
As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:C15 column no 2 is like this:
=VLOOKUP(A1,B1:C15,2)
With parameters such as the value we looking for, range of table array where we would like to find the value that we want, and how far the column of values that we want from the left most column of the table array range.
Unfortunately, this form is rigid, the table array range is fixed to row 1 until row number 15. So this form is not suitable for doing VLOOKUP on the database which always dynamic, increase or decrease from time to time.
Using the above VLOOKUP form, every time there is an addition of new data, we need to change the VLOOKUP formula to match the numbers of expanding data.
We need to change the format of the VLOOKUP formula to be more flexible and save the time we need to manually adjust the formula by hand.
The following changes is the form that we need:
=VLOOKUP($A1,OFFSET($B$1,0,0,COUNTA($B:$B),3),2)
Here we got help from OFFSET Excel function to return the reference of VLOOKUP table array range that we need plus the COUNTA function to calculate the maximum number of rows on the database.
This way, every time there are any reduction or addition of new data on the VLOOKUP table array, COUNTA will return the number of rows needed, and OFFSET will provide the referense of the new table array, so we do not need to make changes the formula manually 
Formula at A2 returning a wrong vlookup value when we use the first vlookup =VLOOKUP(A1,B1:C15,2) formula. This is because the value that we are looking for already outside vlookup table array. Formula at A3 returning the correct value when we use the auto expanding version of the lookup.
Read this great article of Dynamic Ranges for more information about dynamic ranges in Excel.
I am confess, I can not write well, let alone give an easy to read and easy to understand explaination 
I cut and pasted this to Excel, but got an error message “too few parenthesis”.
Hi Bob,
Try this
=VLOOKUP($A3,OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),46),2,FALSE)
In above example values for the col_index_num and [range_lookup] parameters are not provided.
Hi Bob,
Sorry, it’s look like I miss the last parameter from VLOOKUP.
The above sample was fixed now.
Thanks WW245372
I really like your post. Does it copyright protected?
Hi Katty, you can use my blog post as long as you include a backlink to the original blog post in my blog.
Hi, Bob
As I am learning VBA in Excel, I searched for website in Korea (as u guess I am Korean) but I don’t find out it.
So, today, I got u.
Thx u and can I get ur post?
P.S : Plz, understand my English very poor hehe…
Hi, I have three colums with data labeled: Part, Trial and Measurement, I want to use vlookup to lookup the number of trials and create columns labeled trials according to the number of trials in the dataset, then create rows adccording to the number of Parts and add the appropriate measurements to the row column pair.
Neat solution which I thought I may be able to use but not too be! Any ideas on the following:
I have a list if data on which the primary field is not unique. I can find the first entry of my target data with a simple VLOOKUP function. I then want to dynamically change the table_array references to serach the remainder of the table for the next entry. I may need to do this a number of times till I’ve retrieved all of my target entries. For example:
If I use vlookup(a1,b1:g500,1,false) to get my first entry and this is found in cell B27 then how can the function on the next line auto change to vlookup(a1,b28:g500,1,false) (note the B27+1 change for the start of the range) to find the next entry? Thanks in advance.
Hi,
im wondering if you can help me with this. Im trying to extract data from a ‘DATA’ worksheet into other worksheets. I would normally use vlookup however this time, the “lookup_value” is repeated many times so we can’t use the vlookup function anymore. Do you know how i can still do this? I could attach an example spreadsheet and send it to you so that you can understand better if i can have your email.
I hope u can help me with this as ive been cracking my head for a few days already..
Thanks.
Pingback: Swiffer Wetjet Coupon
Pingback: reverse mortgage cons
Pingback: reverse mortgage calculator
Pingback: Attorney in Phoenix
Pingback: acid reflux symptoms
Pingback: walmart money card
Pingback: open office 4
Pingback: Collection agency
Pingback: game
Pingback: collection agency rates
Pingback: fitness and health articles
Pingback: film x
Pingback: Alternative Energy Projects
Pingback: collection agency help
Pingback: rencontre Quebec
Pingback: collection agency newsletter
Pingback: Amir Khan Engagement
Pingback: free credit report and score
Pingback: wedding photographer calgary
Pingback: denver carpet cleaning
Pingback: Wedding Photography
Pingback: Kaspersky Trial
Pingback: schnell abnehmen
Pingback: permanent magnet generator
Pingback: wisdom tooth removal
Pingback: Mike
Pingback: buy facebook fans
Pingback: ????? ??????
Pingback: skin whitening
Pingback: backlinks dofollow
Pingback: Erotiek Groothandel
Pingback: Gaming Headsets
Pingback: Metasuchmaschine
Pingback: pizza hut coupons
Pingback: JB
Pingback: window shutters
Pingback: comparatif assurance auto
Pingback: credit auto
Pingback: Cheap Magnetic Motor
Pingback: tatuaggi lettere
Pingback: Article Directory