MrExcel Publishing
Your One Stop for Excel Tips & Solutions

V lookup updating

Posted by CHRIS on February 19, 2001 9:13 AM

Ive got a Vlookup function running from 1 worksheet and ive got a table of data which is used in the range for the Vlookup. the table is in another worksheet. How would i automatically update the Vlookup Range Every time I add more data in the range via a macro. all the macro does is to copy and paste data thats in some cells into another part of the worksheet.

Posted by Aladin Akyurek on February 19, 2001 10:35 AM

May I suggest a different method: Since your lookup table changes dynamically ("by adding more data" to it), set up a dynamic range for your table.

Assuming that your table currently occupies the range A2:C7:
Step 1. Select this lookup range.
Step 2. Activate Insert,Define,Name. Type for Names in worbook:

DYNTABLE (or whatever name that is meaningful to you)

and enter the following formula for Refers to:


And click OK.

After all this you can use the name DYNTABLE in your VLOOKUP-formulas, and add more data to your table whenever needed.

Note that columns A to C should not contain any data other than your table.