Are there such things as variables within an Excel formula?

Question

I hate repeating functions, particularly in Excel formulas. Is there any way that I can avoid something like:

=IF( VLOOKUP(A1, B:B, 1, 0) > 10, VLOOKUP(A1, B:B, 1, 0) - 10, VLOOKUP(A1, B:B, 1, 0) )

[The above is just a simple example of the problem, and not a particular formula that I'm working with.]

Answer 1

You could define a name for the VLOOKUP part of the formula.

1. Highlight the cell that contains this formula
2. On the Insert menu, go Name, and click Define
3. Enter a name for your variable (e.g. 'Value')
4. In the Refers To box, enter your VLOOKUP formula: =VLOOKUP(A1,B:B, 1, 0)
5. Click Add, and close the dialog
6. In your original formula, replace the VLOOKUP parts with the name you just defined: =IF( Value > 10, Value - 10, Value )

Step (1) is important here: I guess on the second row, you want Excel to use VLOOKUP(A2,B:B, 1, 0), the third row VLOOKUP(A3,B:B, 1, 0), etc. Step (4) achieves this by using relative references (A1 and B:B), not absolute references ($A$1 and $B:$B).

Note:

1. For newer Excel versions with the ribbon, go to Formulas ribbon -> Define Name. It's the same after that. Also, to use your name, you can do "Use in Formula", right under Define Name, while editing the formula, or else start typing it, and Excel will suggest the name (credits: Michael Rusch)

2. Shortened steps: 1. Right click a cell and click Define name... 2. Enter a name and the formula which you want to associate with that name/local variable 3. Use variable (credits: Jens Bodal)

Answer 2

Now you can use the function LET to declare variables within Excel formulas. This function is available since Jun 2020 for Microsoft 365 users.

Given your example, the formula will be:

=LET(MyFunc,VLOOKUP(A1,B:B,1,0), IF(MyFunc > 10, MyFunc - 10, MyFunc ) )

The 1st argument is the variable name and the 2nd argument is the function or range. You can add more pairs of arguments variable, function/range.

After adding the variables, the last argument will be your formula of interest -- calling the variables you just created.

For more information, please access the Microsoft webpage here.

Answer 3

You could store intermediate values in a cell or column (which you could hide if you choose)

C1: = VLOOKUP(A1, B:B, 1, 0)
D1: = IF(C1 > 10, C1 - 10, C1)

Answer 4

Two options:

• VLOOKUP function in its own cell: =VLOOKUP(A1, B:B, 1, 0) (in say, C1), then formula referencing C1: =IF( C1 > 10, C1 - 10, C1 )
• create a UDF:

Function MyFunc(a1, a2, a3, a4)
    Dim v as Variant
    v = Application.WorksheetFunction.VLookup(a1, a2, a3, a4)
    If v > 10 Then
        MyFunc = v - 10
    Else
        MyFunc = v
    End If
End Function

Answer 5

Yes. But not directly.

Simpler way

• You could post Vlookup() in one cell and use its address in where required. - This is perhaps the only direct way of using variables in Excel.

OR

• You could define Vlookup(reference)-10 as a wrapper function from within VBE Macros. Press Alt+f12 and use that function

Answer 6

Defining a NAME containing the lookup is a neat solution, HOWEVER, it always seems to store the sheet name with the cell reference. However, I think if you delete the sheet name in the '' quotes but leave the "!", it may work.