Pull cell results into VLookup formula

dog125544

New Member
Joined
Mar 11, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,936
Messages
5,639,084
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top