SWITCH Formula in Excel

Excel switch formula can be used to check multiple values. With this formula you can check multiple values than in result, you will get the first value that matches. So, if you ever want to match multiple values to find the first value that has a match, then you can use this formula.  

Here is the SWITCH Formula Breakdown;

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

Explanation of SWITCH Formula;

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

Let’s try this on a given simple Job Table (e.g. Raman= YES, Shivam = NO, John = MAYBE), Raman, Shivam, And John are candidates for the job and answers are given in B2 sells if they going to get it or not. Therefore, Now we need to create a SWITCH FORMULA that will stimulate the exact same logic as the table.

Previously In Formulas – How To use SMALL Formula in Excel? (2 SIMPLE STEPS)

Here all the process is mentioned below with step-by-step:

STEP 1: First we are going to enter the SWITCH FUNCTION in a blank cell of the table:

=SWITCH(

SWITCH Formula

STEP2. Now continue with the SWITCH arguments:

EXPRESSION: the value we need to check.

So, for this, we will select the cell which contains the D1 that we want to translate to the correct description.

=SWITCH(E1,

SWITCH Formula1

VALUE1, RESULT1

In this place, we need to write the second lookup value and the value we want to return if it gets matched. So, start from the second value of that D table. Here if the value is Shivam (A2), then the description will be “yes”.

=SWITCH(E1,A2,"YES",

SWITCH Formula3

Value2, result2

In this place we need to write the second lookup value and the value we want to return if it gets matched. So, start from the second value of that D table. Here if the value is Shivam (A3), then the description will be “NO”.

=SWITCH(E1, A2, "YES",A3,"NO"

SWITCH Formula4

Value3, result3

Here we have to write the third lookup value and the value we want to return if it gets matched. So, start from the third value of that D table. If that value is John (A4), then the description will be “MAYBE”.

=SWITCH(E1, A2, "YES",A3,"NO",A4,"MAYBE"

SWITCH Formula5

default 

We need to write the default value to return if nothing gets matched in this formula. So, we will write “Unknown” to show the specified unknown value.

=SWITCH(E1, A2, "YES",A3,"NO",A4,"MAYBE" ,"UKNOWN")

SWITCHFormula6

Here is the result of the correct job description.

SWITCH Formula7

Now let us try an unknown Candidate (Rahul) and show you its resulting description:

SWITCH Formula8