Auto update vlook up without Macro

[ananthram]

Hi All Excel Ninjas,


Can i know how to auto-update a vlook up without using a macro... because i will be updating excel on daily basis so i need a better solution for auto-update.

 

[Hui]

Ananthram


Vlookup, as with all functions, updates automatically every time the data is changed


This occurs unless


1. Calculation is set to Manual instead of Automatic,

goto the Formulas, Calculation Tab and set Calculation to Automatic


2. The data range changes and is now larger than the Vlookup Formula is using

To fix this use what is called Dynamic Ranges

So instead of using:

=Vlookup(A1, B1:C10, 2, False)


Try:

=VLOOKUP(A1,OFFSET(B1:C1,,,COUNTA(B1:B200)),2,FALSE)


Adjust ranges to suit

 

[ananthram]

sir thanks for your quick response... but 1 point i.e. my settings is under automatic calculations only and the range is around 15k data till date

will try with this vlookup formula


and 1 more thing how can i auto update filtered columns


this happens when i update new set of data and doesn't filters the data recently entered