Vlook Up Macro for a Dynamic Range

  • Hi,


    I have a workbook where i have 3 worksheets.


    Sheet 1 Column A has the Consolidated List of information with mixed combination of Alpha Numeric Characters (All the 3 Sheets contains the Similar Data in Column A).
    There is same kind of list information in Sheet 2 and Sheet 3.
    My Requirement is, every time I will get a request to do the comparison between two or 3 files.
    In order to avoid that, is there a way with Macro where we can perform the VLook up function through a click on a button or Something like that.
    Please note that the Macro should work for the Dynamic Range in the Column A.


    Is that Possible?


    I want to give this them to avoid any type of comparison requests coming to me.


    Suggest the best possible way.


    Sample attached.

  • Re: Vlook Up Macro for a Dynamic Range


    Hello Kumarma,


    Could you attach your example? Also I am not sure what you are trying to look up. Do you want to check if the values in sheet1 column A exist in sheet2 or sheet3? Are you looking up every value in column A or just 1? Can we use column B and C to show comparisons between your workbooks or use a calculation fields?


    What this code does is it will look at all the data in Sheet1 column A and compare it to Sheet2 column A and Sheet3 column A. If it finds a match in Sheet2, it will place the value in Sheet1 Column B, if it does not find a match it will leave the cell blank. If it finds a match in Sheet3 then it will place the value in Sheet1 Column C.



    This is the best I can do without more direction.


    Sincerely,


    -Max

  • Re: Vlook Up Macro for a Dynamic Range


    Hi Max,



    Sorry for not not providing the complete information.



    That worked like a charm. I am exactly looking for the same code.



    It will be helpful if that is refined at bit as below,



    I would like a column to be inserted after Column A based on the number of Sheets to be compared to show the mapping result (Even if there is no empty column to show the result).



    If I want to compare Sheet 1 (Column A) list with the Sheet 2, Sheet 3 (Column A everywhere) list, then in Sheet 1 after column A it should insert 2 columns to show the mapping result.



    If I want to compare sheet 1 (Column A) list with sheet 2 (Column A) list, then in sheet 1 after column A it should insert 1 column to show the mapping result.



    If possible I would also like the same thing If there is Sheet 4 as well (comparing 4 sheets).



    And if there is a match then it should display "Matching" else "Not Matching" in all the columns (based on the comparing sheets).



    Is this possible?





    Thanks in advance

  • Re: Vlook Up Macro for a Dynamic Range


    Try this (I tried to annotate the code so it is as easy as possible for you to understand). This will add in a column after column A for every sheet in the workbook except the first sheet. Next it will match your data in column A to each sheet via Vlookup. Lastly it will convert any cells with values to either Matching or Not Matching. This works dynamically for the # of sheets and for the range of data in column A: