?
Solved

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

Posted on 2011-10-11
3
Medium Priority
?
398 Views
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.
0
Comment
Question by:Mswetsky
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 36950057
If you always want to look up column A, then refer to it as RC1 rather than using a relative column:
"VLOOKUP(RC1,P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 36950151
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.

Thanks
Rob H
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 36950419
Thank You
I thought that RC was relative to the currect position.
That is just what I was looking for.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

862 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