Cell number in formula changes based on data array?

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Hi guys, I have a question. Say in Sheet 1 I have a formula like this:

=VLOOKUP(F2,$A$2:$D$7,2,FALSE)

which depends on the data array $A$2:$D$7.

I will use the same formula in other sheets but the data array will change. Like for example, in Sheet 2, the data array is $A$2:$D$15, so the formula I need would be =VLOOKUP(F2,$A$2:$D$15,2,FALSE).

And so on and so forth for other sheets that have a different data array in terms of vertical size. Since there are so many sheets, I don't really want to manually change the array every time. So is there an easy way to change it automatically? Basically I just want the array to change based on the vertical size of the array regardless of the formula.

Any suggestions would be greatly appreciated. Thanks!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016
Hi There

Try naming your Range then referncing it in your formula using

INDIRECT(CellContainingDataArrayName) instead of the array itself.

HTH

Dave
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Hi There

Try naming your Range then referncing it in your formula using

INDIRECT(CellContainingDataArrayName) instead of the array itself.

HTH

Dave

Hi Dave. Sorry, but could you give me an example?
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016
Sure give me 5 minutes to build you a HTML
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Why not just oversize the range to a number larger than any of the sheets? Blank rows do not effect Vlookup Formulas.

=VLOOKUP(F2,$A$2:$D$1000,2,FALSE).

That way, it will work on any sheet that has ranges 1000 rows or smaller.


for that matter, why bother specifying rows at all??

=VLOOKUP(F2,$A:$D,2,FALSE).
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

Here I have two Arrays separated by the VLOOKUP functions

The top array is called Arrays1, the bottom one Arrays2, you will note the INDIRECT function referencing the cell with the named range, this means you can VLOOKUP any value in any Named range you have
Book1
ABCDE
1
2Bob15
3John26
4Dave37
5Mark48
6
7
8Arrays1Bob15
9Arrays2John1212
10
11
12Bob1115
13John1216
14Dave1317
15Mark1418
16
Sheet1


Hope it helps


Dave
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
dave - somethings wrong with your HTML post, can't see the formulas...

By the way, there's no need to use indirect, you can put the named range directly in the formula..

=VLOOKUP(A1,Arrays1,2,FALSE)

Just need to make sure tools - options - calculation - "Accept Lables In Formulas" is checked.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016
Sorry about the error, I had a problem with the HTML Maker had to restart Excel. I have resubmitted. There is a column reference error in the bottom right, I would change it but it purely for demonstration purposes.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,876
Messages
5,766,875
Members
425,383
Latest member
IllDo

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