• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using a Formula Instead of VBA

You are here: Home / Excel Formulas / Using a Formula Instead of VBA
using formula instead of vba
April 26, 2019 by Philip Treacy

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA is not the best answer, using a formula is.

What this tells me is that people don't know how to use Excel's functions and when they come up against a problem they think is difficult, or just don't know how to solve it, they go looking for a VBA solution.

By making yourself familiar with basic functions like IF and VLOOKUP (just to name two) you can make your life a lot easier.

Function or Formula?

You'll often see the terms function and forumla used interchangeably. Strictly speaking however, a function is the actual piece of code written into Excel's core programming that gives you a result based on inputs you give it.

For example, IF is a function but when you use it like this

=IF(A1>10,True,False)
you've written a formula using the IF function.

How Does IF Work Again?

Let's go over how the IF function works before we get any further. It really is very simple and can help understand how other functions work too.

The syntax is IF (Test_Condition, Result_If_True, Result_If_False)

where Test_Condition is some test that can be evaluated to see if it is true or false e.g.

  • A1>10 : Is A1 > 10?
  • A1*0.5=A2 : Is A1 x 0.5 equal to the value in A2?
  • A1>=A2 : Is the value in A1 greater than or equal to the value in A2?
  • A1="Hello" : Does A1 contain the string "Hello"?
  • AND(A1>0,A2<10) : Are both of these conditions true? Is the value in A1 greater than 0 AND is the value in A2 less than 10?
  • SUM(A1:A10)>1000 : Is the SUM of the values in cells A1 through to A10 greater than 1000?

Easy. So once we have our test, IF will determine if it is TRUE or FALSE. If it TRUE then the result returned by the IF function will be whatever you specify in Result_If_True.

If the result of your test is not TRUE then the result of the IF function will be whatever you specify by Result_If_False.

Result_If_True and Result_If_False can be things like a number, a text string, a Boolean Value or even another function. For example:

=IF(A1<=10, SUM(B1:B5), SUM(C1:C5))

means that if the value in A1 is less than or equal to 10, then the result of this IF formula will be the SUM of the values in cells B1 through to B5.

If the value in A1 is greater than 10, then the result of this IF formula is the SUM of the values in cells C1 through to C5.

Now we've covered that, let's look at some examples of where formulas are better than using VBA.

Download The Workbook

All of the problems I'm about to describe, and their solutions, can be found in the example workbook you can download here.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Get the Day From a Date

Given that we have a series of dates, how do we get the day of the week i.e. Monday, Tuesday etc?

I was asked to work out a VBA solution to this but all we need is the TEXT function.

=TEXT(A1,"DDDD")

gives us the day of the week

TEXT Function Formatted for Day of Week

and if we specify a different format we can get the abbreviated day of the week.

=TEXT(A1,"DDD")

TEXT Function Formatted for Abbreviated Day of Week

The TEXT function allows us to format numbers (which is what dates are) in specific ways.

Work Out Commission Rate and Commission Based on Sales

We have a number of sales people and we want to work out their commissions based on how much they have sold.

Table of sales and sales people

The commission rates based on sales are

Commission rates

The original question had a lot more salespeople and commission rates and I think that was the reason a VBA solution was requested. That and the logic required to work out commission based on various sales bands.

Your first thought might be to use IF but as you have to allow for many sales bands, you have to nest a lot of IF functions and that can get very messy and confusing.

All you need is VLOOKUP.

VLOOKUP allows you to look up a value and return an adjacent value.

So we can lookup a value in the Sales column and return the adjacent value from the Rate column.

In the image below, the formula in C3 is looking up the value in B3 from the range F3:G7. It matches against the first column (Sales) and returns the adjacent value in the 2nd column (Rate) of the range F3:G7.

VLOOKUP Commission rate lookup

But the Sales amount in B3 is 19,800. How does VLOOKUP know what to do because our sales bands don't have this exact value?

That's where the last parameter we're supplying to VLOOKUP comes in. By specifying TRUE we're telling VLOOKUP to give us the nearest, lowest, value that matches what we are looking for.

So the nearest, lowest, value to 19,800 is 15,000 so we get 1.5% returned.

To do this you must use VLOOKUP with a sorted list which in this case means having the Sales bands sorted in ascending order.

You can get VLOOKUP to do exact matches too by specifying FALSE for this parameter but that's not what we want here.

What if someone sells less than $10,000 worth of widgets? We don't have a value that VLOOKUP can return in this case so there are two ways to deal with this.

Add a value of 0 with 0 commission to our lookup values

Commission rate allowing for 0 sales

Any volume of Sales below 10,000 will now result in 0 commission.

Or wrap the VLOOKUP in an IFERROR function.

=IFERROR(VLOOKUP(B22,$F$22:$G$26,2,TRUE),0)

If we now ask VLOOKUP to lookup a sales value less than $10,000 it will cause an error but our IFERROR will catch this and give us a 0.

What started off looking like a complicated problem that required VBA was solved just be using VLOOKUP (and IFERROR).

Finding a Haulage Firm Based on the City and Store

Our company sells widgets and we supply various stores across the country. The company we use to transport our widgets depends on what city and what store we are shipping to.

Here's a list of the cities (3 letter abbreviations), stores and haulage firms

List to use with INDEX MATCH

What we want to do is to enter the city and store and have the haulage firm shown in another cell.

I'm using Data Validation in the City and Store columns to allow me to choose from drop down lists.

Using data validation to enter data

This is a little more complicated but if we break it down into steps, it becomes manageable.

The essence of what we are trying to do is lookup a value (the haulier) based on two other values (the city and store).

You could write a monstrous nested IF but as soon as you have more than a few IF statements it becomes very unwieldy and you should stay away from doing that.

We could possibly use VLOOKUP but if our data layout is fixed so we can't insert another column before (to the left) of the Haulier column, we can't use VLOOKUP because it can only return values to the right of the lookup column.

There is a way to use the CHOOSE function with VLOOKUP to do lookups to the left but I will not use that method here.

I'm going to use INDEX and MATCH.

The key here is that we need to create a helper column which consists of the city code and store name joined together. What this means is that we are effectively looking up two things at the same time.

Helper column

INDEX returns the value at the intersection of a row and column. So if we write =INDEX(A1:C5,2,3) the result is the value at Row 2, Col 3 in the range A1:C5.

INDEX function example

The column can be omitted so writing =INDEX(A1:A5,2) means we are looking up the value in just one column (A1:A5) and we want the value in Row 2.

INDEX function on single column

Because each combination of city code and store name is unique, the Helper column is filled with unique entries meaning we can use INDEX to look up where a particular combination of city/store occurs.

But how do we do that dynamically so that we aren't specifying a hard coded number? By using MATCH.

MATCH returns the relative position of a value in a list (a column or row).

If I write =MATCH("SYD",A1:A5) with the sample data below, the result is 4.

MATCH function example

By using MATCH to lookup the position of the values in the Helper column, we can then feed that into INDEX which returns the corresponding value (from the same row) from the Haulier column.

In the image below, the formula in C2 is =INDEX($H$2:$H$17,MATCH(A2&B2,$I$2:$I$17,0))

INDEX MATCH example

We use MATCH to lookup the joined strings in A2 and B2 which is SYDBest Hardware and we get the result 4.

MATCH(A2&B2,$I$2:$I$17,0)

This 4 then indicates to INDEX which haulage firm to return =INDEX($H$2:$H$17,4) which gives us Alpha Haulage.

Why Don't People Use More Functions/Formulas?

Don't get me wrong, VBA is great if you need to automate repetitive or boring tasks, and you need VBA to do certain things that you just can't do any other way.

But knowing the functions available to you in Excel and how to use them in formulas can save you a lot of time pursuing other avenues looking for an unnecessarily complex solution.

Part of the problem is just not knowing what functions are there and what you can do with them. You need to take the time to learn them.

That might seem like a boring, waste of time to familiarise yourself with functions you aren't going to use right away, but in the long run it's so useful.

When I was first starting out in I.T. I remember reading a book on the DOS prompt and all it could do. I used to do a lot of batch scripting and it helped me immensely. I became the guy people came to when they needed help with their scripts.

Thing is, I didn't know any magic. I'd simply taken the time to learn how to use the tools I was using. And it wasn't hard.

If you aren't familiar with Excel's functions, you should make the effort to learn what they can do.

Not only will you get your work done more quickly, your confidence in using Excel will soar.

This is precisely why you should look at our Advanced Formulas Course.

Advanced Formulas Course

The course is designed to get you quickly up to speed with the functions that are going to give you the biggest efficiency gains.

Many of the functions are considered advanced, but when you’ve finished the course you’ll realise that everything is easy once you know it.

The course uses real world examples and covers both the fundamentals for each function and less obvious uses for them. It's these more advanced techniques that will really set you apart from the crowd.

Check out the Excel Formulas Course now.

Excel Formulas Course Syllabus

using formula instead of vba

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:power query approximate match vlookupPower Query Approximate Match VLOOKUP
Next Post:IF Formula BuilderIF Formula Builder

Reader Interactions

Comments

  1. Peter Bartholomew

    April 29, 2019 at 8:31 pm

    Jeff
    I love it! The trouble is that most Excel users, like St. Augustine, would add “but not yet”!

    Reply
  2. jim

    April 29, 2019 at 6:16 pm

    ah, the nostalgia of DOS commands in batch files!
    there was a utility called Stackey that I used to be an expert in which you could use to store up keystrokes to be used when you launched a program (in the days before you could have more than one program running in parallel)

    kids these days, don’t know they’re born!

    Reply
    • Philip Treacy

      April 29, 2019 at 9:27 pm

      they were the good old days Jim πŸ™‚

      Reply
  3. Jeff Weir

    April 29, 2019 at 2:28 pm

    ..or as I like to put it:

    Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and the wisdom to know the difference.

    πŸ™‚

    Reply
    • Philip Treacy

      April 29, 2019 at 3:00 pm

      LOL, very good Jeff.

      Reply
  4. Peter BARTHOLOMEW

    April 27, 2019 at 7:53 pm

    Phillip

    I enjoyed the article. The worksheet is capable of providing far more than a blank canvas for a VBA solution. One area that I have explored with success is the use of Named Formulas to provide an alternative to deeply nested worksheet formulas which can be read as if it were a sequence of code statements.

    I think that one function that is often overlooked is the old LOOKUP that can provide far more readable syntax, especially in its array form, e.g.

    = [@Sales] * LOOKUP([@Sales],CommissionRate)

    where CommissionRate is the name given to the lookup table. Unlike the nation’s favourite, VLOOKUP, LOOKUP works well with Names and does not require a hard-wired column number.

    Reply
    • Philip Treacy

      April 28, 2019 at 9:10 pm

      Thanks Peter, great to have more solutions posted by people πŸ™‚

      Reply
  5. Excel Gazi Deniz Hodja (Ali Muhiddin Haci Bekir)

    April 27, 2019 at 4:22 am

    For the problem of “Finding a Haulage Firm Based on the City and Store” an alternative solution that may lift the need for a helper column is as follows. It is based on the use of the CONCATENATE() function inside the INDEX(MATCH()) function combination as an array formula.

    The formula is to be written in cell C2 in the last screenshot image above and entered as an array formula with CTRL+SHIFT+ENTER.

    =IFERROR(INDEX($H$2:$H$17, MATCH(A2&B2, CONCATENATE($F$2:$F$17,$G$2:$G$17),0)), “Not found” )
    …

    Thx for the great post, Philip.
    You and Mynda along with Chandoo are absoutely the best Excel gurus in the southern hemisphere of the Earth!

    Deniz Hodja

    Reply
    • Philip Treacy

      April 28, 2019 at 9:09 pm

      Thanks for the formula Deniz. Nice to have another solution.

      And thank you for your kind words πŸ™‚

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.