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.

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.

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

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.