Auto Expanding VLOOKUP Table Array

Posted on the April 2nd, 2009 under Excel Formula by Poer @ Excel VBA/Macro

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:D15 column no 2 is like this:

=VLOOKUP(A1,B1:D15,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 6 until row number 3450. 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

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

Related Entries

External Resources

Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: excel 2003 vlookup, excel formulas vlookup, excel vba vlookup, excel vlookup table array, lookup table, microsoft excel vlookup, vlookup array formula, vlookup pivot tables

6 Responses to 'Auto Expanding VLOOKUP Table Array'

RSS

May 4, 2009 at 4:13 am
Bob

I cut and pasted this to Excel, but got an error message “too few parenthesis”.
May 10, 2009 at 5:36 pm
WW245372

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.
June 3, 2009 at 12:04 am
Poer

Hi Bob,

Sorry, it’s look like I miss the last parameter from VLOOKUP.

The above sample was fixed now.

Thanks WW245372
June 15, 2009 at 10:20 am
KattyBlackyard

I really like your post. Does it copyright protected?
June 16, 2009 at 11:23 am
Excel VBA Macro

Hi Katty, you can use my blog post as long as you include a backlink to the original blog post in my blog.
September 9, 2009 at 1:36 pm
Johann Bak.

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…

Excel VBA Macro