Conditional and Table-Lookup Functions in Excel

Conditional Functions

IF (logical-test, value-if-true, value-if-false)

If the logical test evaluates to a TRUE value then the value of the cell containing the IF function is the value of the 2nd argument, while if the logical test evaluates to FALSE then the value of the cell containing the IF function is the value of the 3rd argument.

Here the logical test can be built from the logical operators and comparison operators. For example

$$\begin{array}{ll} =\textrm{IF}(A5 \gt G6, \ldots)\\ =\textrm{IF}(\textrm{AND}(\textrm{NOT}(A7 = B8 + 7), \textrm{SUM}(E2:E7) >= 0), \ldots) \end{array}$$

In addition some special logical functions such as ISERROR, ISBLANK, ISODD, ISEVEN and ISNUMBER are available.

IF statements can also be nested. E.g. the formula

$$\textrm{IF}(A3 \gt 8, \textrm{"GOOD"},\textrm{IF}(A3 \lt 0,\textrm{"NEG"},\textrm{"BAD"}))$$

returns GOOD if the value in cell A3 is larger than 8, it returns NEG if this value is negative and it returns BAD if A3 has a value between 0 and 8 inclusive.

IFS (logical-test1, value-if-test1-true, logical-test2, value-if-test2-true, $\ldots$)

As described above, you can nest IF statements. The problem with this is that the nesting (and especially the placement of parentheses) can become complicated. Excel 2016 introduces a new IFS function which simplifies such formulas. For example, the following are equivalent: $$\textrm{IF}(A3 \gt 8, \textrm{"GOOD"},\textrm{IF}(A3 \lt 0,\textrm{"NEG"},\textrm{"BAD"}))$$ $$\textrm{IFS}(A3 \gt 8, \textrm{"GOOD"}, A3 \lt 0, \textrm{"NEG"},\textrm{TRUE},\textrm{"BAD"})$$

SWITCH (expression, value1, result1, value2, result2, $\ldots$)

Excel 2016 also provides a new conditional function similar to IFS, which returns the first resultN where expression = valueN.

SUMIF ($R_1$, criteria, $R_2$)

$R_2$ is an array of potential values to be summed and $R_1$ is an array of the same shape and size containing values to be matched against the criteria. For each value in $R_1$ that meets the criteria, the corresponding value in $R_2$ is used in the sum. Where the values in $R_1$ are numeric, criteria can take the form of a constant such as 34 or B5, or a logical expression (in quotes) of form ">34", "<>34", "<=34"or ">"&B5.

Note that you need to use the ampersand (i.e. the concatenation operator) when creating logical expressions that refer to a cell's value. Thus the "<="&B5 criteria is met for all cells in R1 which are less than or equal to the value in cell B5.

COUNTIF ($R_1$, criteria)

The value of this function is the number of elements in the array $R_1$ that meets the criteria. While for SUMIF, $R_1$ and $R_2$ are usually one-dimensional arrays (i.e. row or column vectors), for COUNTIF, $R_1$ can be any $m \times n$ array.

Where the values in array $R_1$ are alphanumeric, the criteria takes forms such as "Male" or B5. Wildcards can also be used; e.g. "*ite" selects all values in $R_1$ which end in the letters "ite". Selection is case-insensitive, i.e. "Male" is equivalent to "MALE" or "mALe". You can also use criteria such as ">="&"M" to select all values in $R_1$ which start with the letter M or higher. Some additional examples of criteria and ways to use COUNTIF are given below:

COUNTIF(R,">"&B3*2)
yields the number of cells in range R that are bigger than twice the value in cell B3

COUNTIF(R,10)+COUNTIF(R,-10)
yields the number of cells in range R equal to 10 or -10

COUNTIF(R,"???")
yields the number of cells in R that contain exactly 3 characters

Table Lookup Functions

INDEX($R,r,c$)

Returns the contents of range $R$ (a 2D array) at row $r$ and column $c$

MATCH($x,R,0$)

Returns the row index of $x$ in $R$ if $R$ is a column vector, and the column index of $x$ in $R$ if $R$ is a row vector. Will return #N/A if $x$ does not occur in the specified range $R$. You can test for such an error using ISERROR(MATCH($x,R,0$)). If the last argument is a $1$, the position of the largest value less than or equal to $x$ is returned. If the last argument is a $-1$, the position of the smallest value greater than or equal to $x$ is returned.

Note that when MATCH is looking for a match, formats are also considered. For example, a numerical 6 will not match the string of text version, "6". Matching is not case-sensitive. Wildcard characters may be used: ? stands for any single character, while * stands for any sequence of characters.

OFFSET($x,r,c$)

$x$ is a reference to a cell. Suppose $x$ is in row $r_0$ and column $c_0$. This function returns the value of the cell in row $r_0+r$ and column $c_0 + c$.

Example:
OFFSET($A1,-2,3$) yields the value of the cell 2 rows above and 3 columns to the right of $A1$