Using a "variable" in Excel

Question

I have a pretty large Excel with alot of data, spred across multiple sheets. So what i'm wondering is if their is anyway to make a cell act like a variable. Not really sure how to explain it.

Let's say I have a matrix in Sheet3 that goes from A1:D20 and I wan't to use that matrix in a VLOOKUP formula in Sheet1.

Then I would have to write =VLOOKUP(A1;'Sheet3'!A1:D20;2;FALSE)

Can I instead make a cell in Sheet1 reference that matrix? For example if I put ='Sheet3'!A1:D20 in C1 so that I would only have to write =VLOOKUP(A1;C1;2;FALSE)

I have been trying to find the answer to this question but without any luck. So now i'm mostly just wondering if this is even possible.

Answer 1

You are looking for the Indirect() worksheet function. Indirect's definition is as follows:

Returns the reference specified by a text string.

Where reference would be the cell reference you are wanting to obtain, and text string is the textual representation of your reference.

Essentially, Let's say your cell C1 contained the textual reference: Sheet1!A1:D20. You can then use the Indirect() formula to get this reference as such:

=INDIRECT($C$1)

---

You can also create a dynamically-named range. You can access this menu by:

Formula Tab > Name Manager > New

You can name this range anything that isn't already reserved by Excel, so in this example we will name it lookupRng.

You can make this change dynamically - for example, when the number of rows in your range changes, by using something like:

=OFFSET(Sheet3!$A$1:$D$1,0,0,COUNTA(Sheet3!$A:$A),1)

Which could be called doing: =VLOOKUP(A1;lookupRng;2;FALSE)

Answer 2

Would naming the Range help?

Select the range you desire and assign a Name to it, for example: "Matrix" in the Name Box as shown on the picture below.

Then you can use this name in your VLOOKUP formula.

=VLOOKUP(A1;Matrix;2;FALSE)