CODEWITHSUNDEEP
excel
Paul Ellery
Paul Ellery

Reputation: 1745

Are there such things as variables within an Excel formula?

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

Upvotes: 57

Views: 195451

Answers (9)

Fernando Barbosa
Fernando Barbosa

Reputation: 1134

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.

Upvotes: 44

Tim Robinson
Tim Robinson

Reputation: 54734

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)

Upvotes: 44

SCS
SCS

Reputation: 11

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.

Upvotes: 1

CrisVM
CrisVM

Reputation: 1

I know it's a little off-topic, but following up with the solution presented by Jonas Bøhmer, actually I think that MOD is the best solution to your example.

If your intention was to limit the result to one digit, MOD is the best approach to achieve it.

ie. Let's suppose that VLOOKUP(A1, B:B, 1, 0) returns 23. Your IF formula would simply make this calculation: 23 - 10 and return 13 as the result.

On the other hand, MOD(VLOOKUP(A1, B:B, 1, 0), 10) would divide 23 by 10 and show the remainder: 3.

Back to the main topic, when I need to use a formula that repeats some part, I usually put it on another cell and then hide it as some people already suggested.

Upvotes: 0

Jonas Bøhmer
Jonas Bøhmer

Reputation: 35

Not related to variables, your example will also be solved by MOD:

=Mod(VLOOKUP(A1, B:B, 1, 0);10)

Upvotes: 1

codeape
codeape

Reputation: 100786

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

Upvotes: 2

lprsd
lprsd

Reputation: 87095

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

Upvotes: 1

Jeremy
Jeremy

Reputation: 6670

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.

Upvotes: 0

Patrick McDonald
Patrick McDonald

Reputation: 65421

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)

Upvotes: 7

Related Questions