Dynaimc VLOOKUP

[Nagarajanbala]

I have a vlookup formula like this VLOOKUP(RC[-1],Report!R3C2:R934C4,3,FALSE). I want to make is dynamic. I don't have a choice but to give a cell reference for the starting point (in this case r3c2). However I want to make the array end reference R934C4 to be dynamic. I have the variable Lastrow, which will give me the last row number till which i have data.

Now, How will I send the dynamic array value to the vlookup function? (in this case for R934c4)

 

[bstory84]

I think what you are looking for is how to make your Range(array) reference dynamic. meaning you want your range to expand or contract with its data. Am I correct?

 

[Nagarajanbala]

I think what you are looking for is how to make your Range(array) reference dynamic. meaning you want your range to expand or contract with its data. Am I correct?

Can I put the range reference inside the vlookup? I have used range type with a dynamic values.

 

[bstory84]

Yes you can. If it is just a matter of determining how many rows in a column this can be achieved by using a NAMED RANGE utilizing the OFFSET Function. example: Lets say Column "A" has a constantly changing populated number of rows. today the range is A1:A15 tomorrow it is A1:A500. You could reference all of Column A in it's entirety ("A:A") or you can use this formula in a NAMED RANGE to determine the size for you. FORMULA: =OFFSET(SHEET1!$A$1,0,0,COUNTA(SHEET1!$A:$A),1) . If you use the named range and give a name of "DNR_A" then in your formula you would reference "DNR_A" as the Range(array). Hope this helps.