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.
Download Practice Workbook
Download the practice workbook from here.
4 Ideal Examples of Using Excel Formula to Choose Between Two Values
This tutorial will guide you with four ideal examples to use Excel formula 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.
- Firstly, select cell E5.
- Secondly, to find the Champion, type the following formula in the selected cell:
- Finally, press the Enter key on the keyboard.
- Thus, you will find the Champion (Joseph) in cell E5.
Read More: Advanced Uses of CHOOSE Function in Excel (9 Applications)
2. Apply Excel BETWEEN Formula to Choose Between Two Values
This method will show you the process to choose 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.
- First of all, go to cell C9.
- Next, to find the Final Output, enter the formula below in the cell (C9):
- 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.
- In the first place, select cell C9 by clicking on it.
- Then, to find the Final Output, insert the formula:
- 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.
- 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.
- In the beginning, select cell C6.
- After that, to get the lower number, type the formula below in the selected cell (C6):
- 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:
- First, go to cell C6.
- Then, to find the Higher Value, insert the following formula in cell C6:
- In the end, press the Enter key.
- Ultimately, you will find the output in cell C6 (see screenshot).
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. Furthermore, let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.