Excel VBA, How can I create a Vlookup with a dynamic Lookup value

Posted on 2011-10-11
Last Modified: 2012-08-13
For example
I have a sheet where Col A has an ID and I need to match that ID then find the coresponding col from the Vlookup but the sheet is a monthly record so each month the lookup value needs to change.
In the example following, last month was RC-9
"VLOOKUP(RC-10,P01Currentmonth.csv!R1C24:R158C29,6,FALSE)" Source view
This is just one part of a large macro.
Question by:Mswetsky
    LVL 85

    Accepted Solution

    If you always want to look up column A, then refer to it as RC1 rather than using a relative column:
    LVL 31

    Expert Comment

    by:Rob Henson
    Or rather than using vlookup use straight lookup using syntax:

    Lookup(lookupvalue, lookup array, result array)

    The result array is then the only dynamic range.

    Rob H
    LVL 1

    Author Closing Comment

    Thank You
    I thought that RC was relative to the currect position.
    That is just what I was looking for.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now