Explanation

In this example, the goal is to return the smaller of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MIN function.

MIN function

The MIN function returns the smallest numeric value in the data provided. In Excel, it’s common to use the MIN function with a range like this:

=MIN(range) // minimum value in range

However, because MIN can accept values in separate arguments , you can easily use the MIN function to select the smaller of values in two cells like this:

=MIN(A1,B1) // smaller of A1 or B1
=MIN(A1,C1) // smaller of A1 or C1

This is the approach used in the worksheet shown, where the formula in E5 is:

=MIN(B5,C5)

As the formula is copied down, it returns the value in column B or the value in column C, whichever is smaller. The MIN function can be used to return the smallest value from any type of numeric data. This means you can use MIN to solve a variety of “smallest of” problems:

  • Earlier of two dates
  • Earlier of two times
  • Faster of two times
  • Colder of two temperatures
  • Smaller of two fractions

Alternative to IF function

As this example shows, the MIN function can be used as a compact and elegant replacement for the IF function . For example, in the example shown, the IF function could be used to return the smaller of the two values like this:

=IF(B5<C5,B5,C5)

The basic translation of this formula is “If B5 is less than C5 return B5. Otherwise return C5”. This is a perfectly valid Excel formula, and you will often encounter IF formulas that follow this structure. However, the MIN version of the formula is simpler and contains no redundant references, so less prone to errors:

=MIN(B5,C5)

Note that the references to B5 and C5 appear just once in the MIN version.

Explanation

In this example, the goal is to perform a case-sensitive lookup on the name in column B, based on a lookup value entered in cell E5. By default, Excel is not case-sensitive. This means that standard lookup functions like VLOOKUP , XLOOKUP , and INDEX and MATCH are also not case-sensitive. These formulas will simply return the first match, ignoring upper and lower case. The classic way to work around this limitation is to build a lookup formula that incorporates the EXACT function, which performs a case-sensitive comparison. In a nutshell, we use the EXACT function to generate an array of TRUE and FALSE values, then alter the lookup formula to look for the first TRUE value. The article below explains how to use this approach with INDEX and MATCH, and XLOOKUP.

EXACT function

The EXACT function compares two text strings in a case-sensitive fashion. If the two strings are exactly the same, taking into account upper and lower case characters, EXACT returns TRUE. Otherwise, EXACT returns FALSE. For example:

=EXACT("apple","apple") // returns TRUE
=EXACT("Apple","apple") // returns FALSE

If we use the EXACT function on a range of values, we will get back multiple results. For example, if we use EXACT to compare the value in cell E5 with the range B5:B14:

EXACT(E5,B5:B14)

We get back an array that contains 10 TRUE and FALSE values like this:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

This is because we are checking the name in E5 (“JILL SMITH”) against all 10 names in the range B5:B14. Notice the only TRUE value is in the 5th position. This corresponds to cell B9, which contains “JILL SMITH”.

INDEX and MATCH solution

In the worksheet shown, the formula in cell F5 is:

=INDEX(C5:C14,MATCH(TRUE,EXACT(E5,B5:B14),0))

Working from the inside-out, EXACT is configured to compare the value in E5 against all names in the range B5:B14:

EXACT(E5,B5:B14) // returns 10 results

The EXACT function performs a case-sensitive comparison and returns TRUE or FALSE as a result. Because we are checking the name in E5 (“JILL SMITH”) against all 10 names in the range B5:B14, we get back an array of 10 TRUE and FALSE values like this:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

This array is returned directly to the MATCH function as the lookup_array :

MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},0)

Notice the lookup_value given to MATCH is TRUE, and match_type is set to zero (0) to force an exact match. The lookup_array is created by the EXACT function. MATCH then returns 5, since the only TRUE in the array is at the fifth position. This result is returned directly to the INDEX function as the row number, so we can simplify the formula to:

=INDEX(C5:C14,5) // returns 39

With a row number of 5, INDEX returns the age in the fifth row (39) as a final result.

Note: this is an array formula and must be entered with Control + Shift + Enter in older versions of Excel .

XLOOKUP solution

The XLOOKUP function can be configured to perform a case-sensitive lookup using EXACT in the same way as INDEX and MATCH, but in a more compact formula:

=XLOOKUP(TRUE,EXACT(J5,B5:B14),C5:C14)

Notice the lookup_value and lookup_array are configured like the MATCH function above. After EXACT runs, we have the same array of 10 TRUE and FALSE values explained previously:

=XLOOKUP(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},C5:C14)

XLOOKUP returns the 5th item from the range C5:C14 (39) as a final result.

Note: For a more detailed example of a case-sensitive XLOOKUP formula, see this page .