The Excel IF function tests the condition and returns a value if the condition evaluates to TRUE or another value if FALSE. The Excel SWITCH function compares the value against the list of values and returns the result that corresponds to the first match found.
IF function
Below is the simple IF formula that reads "return Pass for scores greater than or equal to 50 and Fail for scores less than 50":
=IF(B3>=50,"Pass","Fail")
IF and VLOOKUP
IF can be combined with the VLOOKUP function. For the below dataset, I want to perform a vertical lookup that can extract the quantity from the data based on the lookup value in D2. If the quantity of the product is greater than 0, the formula returns "Available", otherwise "Not available":
=IF(VLOOKUP(D2,A3:B14,2,0)>0,"Available","Not available")
SWITCH function
For the following dataset, I want the SWITCH formula to return the status "Promotion" if the review number is 1, "Bonus" if 2 and "Good job" if 3. To accomplish the task, I construct the following formula in C3 and drag the fill handle down to copy the formula to the rest of the cells:
=SWITCH(B3,3,"Good job",2,"Bonus",1,"Promotion")
SWITCH and VLOOKUP
Like the IF function, SWITCH can also be combined with the VLOOKUP function. For the below dataset, I will perform a lookup across two lookup tables based on SWITCH and VLOOKUP. Both the tables contain corresponding prices.
Now I want to perform a vertical lookup that can extract the price from the correct table. To accomplish the task, I construct the following formula in D3 and drag the fill handle down to copy the formula to the rest of the cells:
=SWITCH(C3,$F$1,VLOOKUP(A3,$F$3:$H$7,3,0),$J$1,VLOOKUP(A3,$J$3:$L$7,3,0))
You have successfully learnt about the Excel IF and SWITCH functions! I hope this post helped you.
