looping column worksheetfunction vlookup

  • Hi!
    I have used the application.worksheetfunciton method for a vlookup.


    Thiss does the job for one column, but how would I loop this for several columns? Assuming the Table1 stays constant.


    Thanks!

  • Re: looping column worksheetfunction vlookup


    Hello,


    Not sure to fully understand your question ... meanwhile you can test following



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: looping column worksheetfunction vlookup


    Hi, I'm trying to achieve the application.worksheetfunction for vlookup, which works perfectly fine with the code above, but it only works at returning one column of data. How to change this code so that it can be used to return several columns of data without having to copy paste this above code over and over again for each column? So for example if I wanted to perform vlookup on 10 columns pertaining to the same ID number in each column, how would I change the code perform a loop to achieve this? Copying the above code 10 times would work perfectly fine, but it would be very very slow.

  • Re: looping column worksheetfunction vlookup


    Hello again,


    From a macro point of view ... do you mean that [COLOR="#FF0000"]2[/COLOR] should be a variable ?


    i.e Application.WorksheetFunction.VLookup(cl, Table2, [COLOR="#FF0000"]2[/COLOR], False) ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: looping column worksheetfunction vlookup


    Instead of using multiple Vlookups function where the column changes you could use one Match function to find the row where cl is in the table.


    Then you could use Cells to return the values you want, or you might even be able to use resize and transpose.

  • Re: looping column worksheetfunction vlookup


    forum.ozgrid.com/index.php?attachment/72598/
    Hi!
    I have attached a file with the formulas inside the cells, The coding above works perfectly well, but its so slow as I am using the same code for each column I wanna lookup. there maybe thousands of rows and hundreds of columns soon, so there must be a more dynamic way of coding this?

  • Re: looping column worksheetfunction vlookup


    What is the VLOOKUP formula you need for Sheet 1 columns E, F, G and so on?


    Are you looking for a VBA solution or a direct formula solution?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: looping column worksheetfunction vlookup


    Hello,


    There is no need for a macro ...


    In cell B7, you have formula :


    Code
    =INDEX(Sheet2!$A$7:$KN$800,MATCH($A7,Sheet2!$A$7:$A$800,0),COLUMN())


    And copy it to all your potential rows and columns ...
    Hope this will help

  • Re: looping column worksheetfunction vlookup


    Hi thanks for the suggestion really appreciate it! But I would like the sheet to be formula free so this doesn't really solve the problem. How to do the vlookup formula free in vba?

  • Re: looping column worksheetfunction vlookup


    Hello,


    You are welcome ... :wink:


    Similarly, you can use following code :



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!