Question

Which functions can be used as an alternative to the 'IF' function for elaborate tests and why? Explain its various types with syntax.

Hint:

LOOKUP replaces long nested IF formulas. It has a vector form with three arguments and an array form with two arguments.

Answer 1

Step 1: Name the function and the reason:
The LOOKUP function can be used as an alternative to the IF function.
A nested IF formula can test only a limited number of conditions, because Excel limits the nesting of IF functions.
For an elaborate test, or a test that goes beyond that limit, LOOKUP is the better choice.
It searches a range for a value and returns the matching result in one step, so no long chain of IF is needed.

Step 2: Types of LOOKUP function:
LOOKUP has two forms.
1. The vector form.
2. The array form.

Step 3: Vector form with syntax:
The vector form looks in a range of one row or one column for a value.
It then returns a value from the same position in a second range of one row or one column.
Syntax: LOOKUP (lookup_value, lookup_vector, result_vector)
lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
lookup_vector is a range that has only one row or one column. Its values can be text, numbers or logical values.

Step 4: Array form with syntax:
The array form looks in the first row or first column of an array for the stated value.
It then returns a value from the same position in the last row or last column of the array.
Syntax: LOOKUP (lookup_value, array)
lookup_value is the value that LOOKUP searches for in the array. It can be a number, text, a logical value, or a name or reference that refers to a value.

Step 5: Rules of the array form:
If LOOKUP cannot find the lookup_value, it uses the largest value in the array that is less than or equal to the lookup_value.
If the lookup_value is smaller than the smallest value in the first row or column, LOOKUP returns the #N/A error value.

Final Answer:
The LOOKUP function replaces the IF function for elaborate tests. Its two types are the vector form, LOOKUP (lookup_value, lookup_vector, result_vector), and the array form, LOOKUP (lookup_value, array).