Auto Expanding VLOOKUP Table Array
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 
Related Entries
Sum Unique/Distinct Values in Excel
Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only. Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we have...
Get Position of Last Column Containing Data with Excel VBA
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel. The VBA code is the exact replica of the code to get the last row, only...
Convert Excel Column Number into Column Name
Update: Sorter version of the VBA function:Function Number2Char(c As Integer) As String Number2Char = Split(Cells(1, c).Address, "$")(1) End FunctionCourtesy of The Plaid Cow. ORFunction Number2Char(ByVal vNumber)Dim addr As String If vNumber 256 Then Exit Functionaddr = Range("A1").Offset(0, vNumber - 1).Address Number2Char...
How To Find the Last Row That Contain Data in Excel?
UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...
Move Cursor To One Cell Below Last Row With Data
Ever want to paste some data into new empty cell after the last cell with data in certain column? Then take a look at this simple macro.'first, select cell in the first row of that column, like A1, K1, etc Range("A1").Select'move to the last...
Valuable 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.
- Top Tips & Tricks to Get You Started in Microsoft Excel 2007
This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.
- Microsoft Office 2003/XP/2007 Training Videos
Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.
If you found this page useful, please consider bookmark it using social media or add a link to this page.
Incoming search engine terms: vlookup excel vba, VLOOKUP macro, vlookup example, vlookup VBA macro, Excel VBA VLOOKUP, vlookup macro example, vba vlookup, vba macro vlookup, VLOOKUP Array, excel vba vlookup example, vlookup in excel macro, excel array formula tutorial, vlookup vba, table_array example, set up macro to add vlookup