Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to use the SWTICH and the FILTER Functions to create a dynamic report. Let’s get started

Video

SWITCH Function

The SWITCH Function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

Syntax

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

FILTER Function

The FILTER function allows users to filter a range of data based on criteria defined

Syntax

=FILTER(array, include, [if_empty])

In the sample dataset screenshot below, we have Sales report from operating States of California, New York, Florida and Washington respectively. In addition, we have formatted the dataset in an Excel Tables using the respective State names as Table names.

In the Calculation sheet, we have dropdown in cell A2 that contains the list of the four States. We have another dropdown in cell B2 and C2 respectively. The cell B2 contains year 2014 to 2017 and the cell C2 contains $55,000 which is part of the criteria we will be using

Note, this is a ficticious dataset.

SWITCH & FILTER Functions

Our goal is to write a single formula that will filter the dataset from from the four worksheets using the dropdowns in cells A2 to C2 respectively. To achieve this, we will use the FILTER function and the SWITCH function.

• Therefore, in cell C6 of the Calculation sheet, execute the formula: =SWITCH(A2,F1,FILTER(California,(California[Year]=B2)*(California[Sales]>=C2)),F2,FILTER(New_York,(New_York[Year]=B2)*(New_York[Sales]>=C2)),F3,FILTER(Florida,(Florida[Year]=B2)*(Florida[Sales]>=C2)),F4,FILTER(Washington,(Washington[Year]=B2)*(Washington[Sales]>=C2)))
• Click Enter

Based on the relatively long formula above, we referenced cell A2 which is the SWITCH function input value for the expression argument. Then, for the value1, we referenced cell F1. For the result1, we used the FILTER function to filter the dataset for the State of California using the two criteria – Year that equals to 2015 and Sales which is greater than or equal to $55,000. Then, we did the same for the States of New_York, Florida and Washington respectively.

From the result screenshot below, we have the filtered report for the State of California.

So, we can easily switch from cell A2 to choose another State and even choose another Year and the formula will update automatically as we can see in the screenshot below

See you in the next tutorial