VLOOKUP Issues

  • I need help with making vlookup go all the way across my columns and down. Is it possible to have lookup_value and column_index both be variable values? If it isn't possible, is there another way this can be done?



    As of right now, I got the vlook up to go across, but the the cell value stays the same all the way across, I would need it to change going from one column to another, if this makes sense.


    [Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gibson.jpg]

  • Re: VLOOKUP Issues


    The problem is that in each cell it is 'finding the same values, C7 and N5. You could do this 2ways I think...


    1). Copy this formula into cell N7, and then use Fill to drag across the sheet. Excel will then update the necessary references as normal.


    2). Alter your code to make it so that C7 and N5 are dependant on which cell is being filled. Potentially much harder but probably prettier.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: VLOOKUP Issues


    Example of FIll:


    .


    HTH


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: VLOOKUP Issues


    Hello zuko,

    Quote

    I need help with making vlookup go all the way across my columns and down. Is it possible to have lookup_value and column_index both be variable values? If it isn't possible, is there another way this can be done?

    Possibly, like this...

    Code
    For Each ws In Worksheets
            If Not ws.Name = "Sheet1" Then
                ws.Select
                Range(Cells(7, 14), Cells([B]nRow, nCol[/B])) = _
                    "=VLOOKUP(C7,Sheet1!$A$2:$BB$3000,Match(N5,Sheet1!$D$1:$BB$1,FALSE)+3,False)"
                Range("A1").Select
            End If
        Next ws
        Sheets("Sheet1").Select
  • Macro to run a macro in another workbook


    Hi i want to rit a macro which in the background opens an existing excel file runs a macro in the that file copies the generated values from the file back to the first excel file.


    Please help me Im not very much familiar with excel


    TIA


    talshe

  • Macro to run macro in another workbook


    Hi i want to rit a macro which in the background opens an existing excel file runs a macro in the that file copies the generated values from the file back to the first excel file.


    Please help me Im not very much familiar with excel


    TIA


    talshe

  • Re: VLOOKUP Issues


    Ahh! I think I get it.
    In which case the best way and easiest way to do this is to set a row of numbers in the row above your data, (row 4). Starting with 4 in G4 and then 5 in H4...all the way along. You can then hide this column or colour the cells white so they are hidden. Then link the column number to those hidden values.


    EG: =VLOOKUP($C7,DBSS!$A$2::$X$396,G$4,FALSE) then you can copy this down and the column reference will update according to the row number. Can also be done in VBA if you really want, but this is so much simpler!


    Alastair


    EDIT: Once this is doen for the first cell you can again use AutoFill in the same way as my original suggestion or you can use:


    Sub test()



    As an extension to your original code.


    HTH

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: VLOOKUP Issues


    You could also reference the column and adjust as desired within your code. if your lookups increment by one as they go across.


    For example, Column G is the 7th column and you wish to lookup the 4th column in your lookup table. Thus subtracting three from your column will give you the right number. Of course, this won't work well if you want the 6th column of data in your lookup table when you go to column H.


    Just a thought, providing your data "cooperates".


    HTH,

Participate now!

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