Pull cell results into VLookup formula

[dog125544]

Hi All,

I have a problem that has left me a bit stumped. I created a Vlookup on a range, but recognize with time the file could grow--particularly the row numbers. I Initially set my code to cover until row 50, but I would like it to dynamically change as the file grows.

This is the formula that I attempted:
=VLOOKUP(A1,Data!A1'a Template'!F3),2,FALSE)

I am trying to input this code into the 'a Template' sheet.

This is the formula and answer in F3 in 'a Template'
="O"&M14 results in O97

M14 is a an entry of 97 which I type in and change at will. Eventually, I will make this a code as well and I'm confident in that code (=ROW(OFFSET(Data!A1,COUNTA(Data!A:A)-1,0))). I would merge these two codes, but if I can't make the simplified version of my first formula work, it certainly won't work with this additional information.


Is there a way to dynamically change the range in the Vlookup? Or am I totally off base?

 

[Fluff]

Hi & welcome to MrExcel.
Simplest & best option (IMO) would be to use a structured table to hold the data, that way your formulae will always be looking at all the data & it removes the need for volatile functions.

Excel Tables

Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.

exceljet.net