How to Use Excel Formula to Choose Between Two Values

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we may need to pick one value between two given values in Excel. In this article, we will learn how to use the formula to choose between two values in Excel. Here, we will use some built-in Excel functions to create those formulas. So, without further delay, let’s get started.


Using Excel Formula to Choose Between Two Values: 4 Ideal Examples

This tutorial will guide you with four ideal examples of using Excel formulas to choose between two values. In order to demonstrate the examples, we will use some datasets in Excel. Besides, we will describe the steps with screenshots so that you can understand them easily. Let’s see the methods below.


1. Select Between Two Values Using Formula with CHOOSE Function

In the first method, we will use the CHOOSE function in Excel to select between two values. The CHOOSE function in Excel is supposed to return a value from a list depending on a given position. In this case, we will use the dataset below that contains the Participants list (B5:B6) in a lottery. At this time, we need to find the Champion (E5) based on Serial No. (E4). Follow the steps below to find the Champion.

Select Between Two Values Using Formula with CHOOSE Function

Steps:

  • Firstly, select cell E5.
  • Secondly, to find the Champion, type the following formula in the selected cell:
=CHOOSE(E4,B5,B6)

  • Finally, press the Enter key on the keyboard.
  • Thus, you will find the Champion (Joseph) in cell E5.


2. Apply Excel BETWEEN Formula to Choose Between Two Values

This method will show you the process of choosing between two values by creating the BETWEEN formula in Excel. Therefore, we will use some built-in Excel functions to develop the formula. For example, we will select between two numbers, then two dates, and finally two texts here.


2.1 Choose Between Two Numbers

In this approach, we will use the BETWEEN formula to choose between two numbers. Here, we will use the IF function and the AND function in Excel to make the BETWEEN formula. However, we will use the dataset (B4:C9) below containing a Number to be Checked (C4), Conditions (C5:C6), and output values if the conditions are True or False (C7:C8). We want to keep the Final Output in cell C9. The steps are below.

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

  • First of all, go to cell C9.
  • Next, to find the Final Output, enter the formula below in the cell (C9):
=IF(AND(C4>=C5,C4<=C6),C7,C8)

  • Therefore, press the Enter button to get the result.
  • In this way, you will find the Final Output (150) in cell C9 (see screenshot).

🔎 How Does the Formula Work?

  • AND(C4>=C5,C4<=C6): Checks if the two conditions (C4>=C5,C4<=C6) are satisfied or not.
  • IF(AND(C4>=C5,C4<=C6),C7,C8): Returns the C7 as the output if the conditions are met otherwise returns C8.

2.2 Select Between Two Dates

Suppose, we have a dataset (B4:C9) in Excel. It contains a Date to be Checked (C6), two Conditions (C4:C5), and outputs (C7:C8) based on the conditions. Now, we need to check if the Date in cell C6 falls between the two dates in cells C4 & C5 using the BETWEEN formula. We will use the IF function and the MEDIAN function to create the formula. See the steps below.

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

  • In the first place, select cell C9 by clicking on it.
  • Then, to find the Final Output, insert the formula:
=IF(C6=MEDIAN(C4:C6),C7,C8)

  • Lastly, press the Enter key to get the result.
  • See the Final Output in the following picture.

🔎 How Does the Formula Work?

  • MEDIAN(C4:C6): Returns the middle one among the three dates by arranging them in ascending order: 25/9/2017, 7/8/2020, 27/8/2021.
  • IF(C6=MEDIAN(C4:C6),C7,C8): Returns C7 (OK) if the condition (C6=MEDIAN(C4:C6)) is satisfied and otherwise returns C8 (Not OK).

2.3 Find Between Two Texts

Let’s say, we have a dataset (B4:C9) in Excel containing a Text to be Checked (C4), First and Second Conditions (C5, C6), and the outputs if True or False (C7, C8). Now, we will create a BETWEEN formula using the IF & AND functions to find the Final Output in cell C9. Follow the steps below to do so.

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

  • To begin, activate cell C9.
  • After that, to find the Output, type the formula below in cell C9:
=IF(AND(C4>=C5, C4<=C6), "OK", "Not OK")

  • In the end, press the Enter button.
  • As a result, you will find the output in cell C9.
  • Finally, see the result in the following image.

🔎 How Does the Formula Work?

  • AND(C4>=C5, C4<=C6): Checks if the two conditions (C4>=C5,C4<=C6) are satisfied or not. It compares the three texts based on the alphabetical order: Cow, Fox, Goat.
  • IF(AND(C4>=C5, C4<=C6), “OK”, “Not OK”): Returns the OK if the conditions are satisfied otherwise returns Not OK.

Read More: How to Use CHOOSE Function to Perform IF Condition in Excel


3. Pick Lower Value Between Two Numbers with Excel Formula

Assuming, we have a dataset (B4:C6) in Excel containing two Numbers in cells C4 and C5. Now, we will find the lower one between these two numbers using the MIN function in Excel. It returns the minimum value in a list of numbers. The steps to apply this function to pick the lower number are below.

Pick Lower Value Between Two Numbers with Excel Formula

Steps:

  • In the beginning, select cell C6.
  • After that, to get the lower number, type the formula below in the selected cell (C6):
=MIN(C4,C5)

  • Thereupon, press Enter.
  • Thus, you will get the Lower Value (160) in cell C6.


4. Apply Formula to Find Higher Value in Excel

In this method, we will use the MAX function in Excel to find the higher value between two numbers. The Max function returns the highest value in a list of numbers. Now, we will use the dataset (B4:C6) below that contains two numbers in cells C4 and C5. To demonstrate this method, we used the following steps:

Apply Formula to Find Higher Value in Excel

Steps:

  • First, go to cell C6.
  • Then, to find the Higher Value, insert the following formula in cell C6:
=MAX(C4,C5)

  • In the end, press the Enter key.
  • Ultimately, you will find the output in cell C6 (see screenshot).

Read More: Advanced Uses of CHOOSE Function in Excel


Download Practice Workbook

Download the practice workbook from here.


Conclusion

Undoubtedly, the above tutorial will be helpful for you to use the formula in Excel to choose between two values. Even so, download the practice workbook and give it a try.


Related Articles


<< Go Back to Excel CHOOSE Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo