Dynamic vlookups through vba

[sahilkh18]

Hello

I have two files: an input file and output file. I want to apply vlookups in 16 columns from the input file into the output file.

I have written a code in which i am first copying the input data into the output file and then applying the vlookups. I am facing an issue in making the vlookups dynamic.

For example, I want to change the lookup value (rows) and the columns in which the formula is being applied.

I tried to make it dynamic using a loop and incrementing the values, but did not have much luck.

Any help would be appreciated. Files attached for reference.

Input file name: Input File
Output file name: FY20Q4_Spend_Sponsorships
Sheet in which lookups to be executed: Dynamics Tracker
Input sheet where data is being pasted: Data sheet

Column references are updated in the first row of the Dynamics Tracker sheet for reference.

I can email the files, as I can't seem to upload them here.

 

[offthelip]

There is a much better way of doing what you want without using Vlookup and that is to do the entire process using VBA and not using the Vlookup function. This adds as much flexibility as you want to program in and is likely to be a lot faster too. . You haven't given enough details to give exact guidance, but have a look at this thread which gives you the idea of how to go about it:
Vlookup VBA Alternative

 

[Fluff]

Cross posted Vlookup in vba using dynamic ranges - OzGrid Free Excel/VBA Help Forum
and also Dynamic vlookups through vba

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

If you have posted this on any other sites please supply the link(s)