Refresh vlookup data set

[Arjun Dsouza]

Dear Excel Gurus,

Good day, I need your help with vlookup data set changes. Initially when I had created the document (attached) Sheet1 ColA6 - A15 and ColC6 - C15 does the look up to Sheet "DATA" which has information from 01/01/2017 to 07/31/2018 (Column B4 onwards).

I have now updated information until 10/31/2018 and will continue on a monthly basis.

My question - how do I refresh the vlookup formula to give the information from 01/01/2017 to 10/31/2018 and moving.

Thanks in advance for your valuable assistance always.

Kind regards
Arjun

 

[Chihiro]

Use dynamic named range.

Ex: lDate:=DATA!$B$4:INDEX(DATA!$AJ:$AJ,COUNTA(DATA!$B:$B)+2)

And use that to replace... 'DATA!$B$4:$AJ$1562' in your formula.