All You Need to Know About

Logical Formulas in Excel

Logical Functions in Excel are a very core part of your Excel toolkit. Learn about the different Excel Logical Formulas here so that you know what to use for each problem!

Here are the top things on what you can do with Logical Formulas in Excel:

BETWEEN Formula in Excel

There is no explicit Between formula in Excel, however we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?

We have three possible scenarios: numbers, dates, and text.

I explain how you can do this in the Between Formula Example below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: For numbers, we have a creative use of the Median formula:

between-01

=IF(C7=MEDIAN(A7:C7), “Yes”, “No”)

In our first example above, the range is 20-60, upon checking the value 50, it is in between this range.

The median formula will return the value in the middle of these 3 values when arranged in increasing order: 20, 50, 60. The median value is 50. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (50) is in between the range.

STEP 2: For dates, we have the same application of the median formula. Because Excel treats dates as numbers too:

between-02

=IF(C10=MEDIAN(A10:C10), “Yes”, “No”)

In our first example above, the range is May 1 – July 1, upon checking the date June 1, it is in between this range.

The median formula will return the value in the middle of these 3 dates when arranged in increasing order: May 1, June 1, July 1. The median value is June 1. Since it matches the value we are evaluating, then the answer we get is a Yes, this value (June 1) is in between the range.

STEP 3: For text, we are checking if the value is alphabetically in the middle. We will be using the and formula:

between-03

=IF(AND(C12>=A12, C12<=B12, "Yes", "No")

Interestingly enough, you can compare texts using the >= and <= operators.  Excel is able to compare them which goes alphabetically first or last.

In our first example above, the range is Cat – Dog, upon checking the text Cow, it is in between this range. As when arranged alphabetically, it would be: Cat, Cow, Dog.

The And formula checks if Cow >= Cat, and Cow <= Dog. You will see that both of these are true, as Cow is alphabetically later than Cat, while Cow is alphabetically ahead of Dog. Which is why we get a Yes result.

FILTER Formula in Excel

What does it do?

Filters a table array based on the filtering condition given

Formula breakdown:

=FILTER(array, include, [if_empty])

What it means:

=FILTER(data to be filtered, the filtering condition, [value to display if nothing gets matched])


Did you know that you can now filter your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s FILTER Formula. It is a new formula introduced in Office 365 released in 2018!

We have a tax table that we want to dynamically filter with a given rate.

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the FILTER function in a blank cell:

=FILTER(

FILTER Formula in Excel

STEP 2: The FILTER arguments:

array

What is the data to be filtered?

Select the cells containing the tax data, do not include the headers:

=FILTER(C9:D14,

FILTER Formula in Excel

include

What is your filtering condition?

We want to filter the tax rate that is greater than the specified rate. Type in the condition as the tax rate column > the specific tax rate.

=FILTER(C9:D14, D9:D14>G8

FILTER Formula in Excel

[if_empty]

What is the value to display in case nothing gets matched?

Just place an empty string to be displayed if nothing gets matched.

=FILTER(C9:D14, D9:D14>G8, “”)

FILTER Formula in Excel

Try it out now with different values and see it get filtered magically!

FILTER Formula in Excel

FILTER Formula in Excel

SWITCH Formula in Excel

What does it do?

Matches multiple values and returns the first value that has a match

Formula breakdown:

=SWITCH(expression, value1, result1, [value2 / default, result2], …)

What it means:

=SWITCH(value to check, value to match against, result to return, [succeeding values to match or the default value if nothing gets matched], …)


If you have multiple values to check, we can use Excel’s SWITCH Formula! It allows us to specify multiple values to check, then the SWITCH Formula will look for the first value that gets matched!

Let us try it out on a simple ratings table (e.g. 1 = Bad, 2 = Average, 3 = Great), then we will create a SWITCH Formula that will simulate the exact same logic of the table!

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the SWITCH function in a blank cell:

=SWITCH(

SWITCH Formula in Excel

STEP 2: The SWITCH arguments:

expression

What is the value to check?

Select the cell containing the rating that you want to translate to the correct description

=SWITCH(G8, 

SWITCH Formula in Excel

value1, result1

What is the first lookup value and value to return if it is matched?

Let us start from the first value of the rating table. If the value is 1, then the description is “Bad”

=SWITCH(G8, 1, “Bad”

SWITCH Formula in Excel

value2, result2

What is the second lookup value and value to return if it is matched?

Let us start from the second value of the rating table. If the value is 2, then the description is “Average”

=SWITCH(G8, 1, “Bad”, 2, “Average”,

SWITCH Formula in Excel

value3, result3

What is the third lookup value and value to return if it is matched?

Let us start from the third value of the rating table. If the value is 3, then the description is “Great”

=SWITCH(G8, 1, “Bad”, 2, “Average”, 3, “Great”,

SWITCH Formula in Excel

default

What is the default value to return if nothing gets matched?

We want to show the value “Unknown”, if an unknown rating is specified.

=SWITCH(G8, 1, “Bad”, 2, “Average”, 3, “Great”, “Unknown”)

SWITCH Formula in Excel

You now have your correct rating description!

SWITCH Formula in Excel

Let us try an unknown rating (40) and see the resulting description:

SWITCH Formula in Excel

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]