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

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 2

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 3

There isn't a way to define a variable in the formula bar of Excel. As a workaround you could place the function in another cell (optionally, hiding the contents or placing it in a separate sheet). Otherwise you could create a VBA function.

Answer 4

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 5

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).