How to Use Excel Formula to Choose Between Two Values

Method 1 – Select Between Two Values Using Formula with the CHOOSE Function

We will use the dataset below that contains the Participants list (B5:B6) in a lottery. We need to find the Champion (E5) based on Serial No. (E4).

Select Between Two Values Using Formula with CHOOSE Function

Steps:

Select cell E5.
Use the following formula in the selected cell:

=CHOOSE(E4,B5,B6)

Press the Enter key on the keyboard.

Method 2 – Apply the BETWEEN Formula to Choose Between Two Values

Case 2.1 – Choose Between Two Numbers

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.

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

Go to cell C9.
Enter the formula below in the cell:

=IF(AND(C4>=C5,C4<=C6),C7,C8)

Press the Enter button to get the result. You will find the Final Output (150) in cell C9 (see screenshot).

How Does the Formula Work?

AND(C4>=C5,C4<=C6): Checks if both of the two conditions (C4>=C5,C4<=C6) are satisfied.
IF(AND(C4>=C5,C4<=C6),C7,C8): Returns C7 as the output if the conditions are met. Otherwise, it returns C8.

Case 2.2 – Select Between Two Dates

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. We need to check if the Date in cell C6 falls between the two dates in cells C4 and C5 using the BETWEEN formula.

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

Select the cell C9.
Insert this formula:

=IF(C6=MEDIAN(C4:C6),C7,C8)

Press the Enter key to get the result.

How Does the Formula Work?

MEDIAN(C4:C6): Returns the middle value of the three dates 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 or returns C8 otherwise (Not OK).

Case 2.3 – Find Between Two Texts

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).

Apply Excel BETWEEN Formula to Choose Between Two Values

Steps:

Select cell C9.
Use the formula below in cell C9:

=IF(AND(C4>=C5, C4<=C6), "OK", "Not OK")

Press the Enter button.

How Does the Formula Work?

AND(C4>=C5, C4<=C6): Checks if the two conditions (C4>=C5,C4<=C6) are satisfied. 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.