Question

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

Accepted Answer

The look up function can be used as an alternative to the IF function for elaborate tests or tests that exceeds the limit for nesting of IF function. (1) LOOK UP (Vector form): It looks in a one row or one column range for a value and then returns a value from the same position in a second one row or one column range. (½) Syntax: LOOK UP (lookup value, lookup vector, result vector) (1) lookup value: can be a number, text, logical value or a name or reference that refers to a value. (½) Lookup vector: is a range that contains only one row or one column, the values in lookup vector can be text, numbers or logical value. (½) Look UP array form looks in the first row or column of an array for the specified value and then returns a value from the same position in the last row or column of the array. (½) Syntax LOOKUP (lookup value, array) (½) • lookup value is a value that Lookup searches for in an array. It can be a number, text, a logical value, or a name or reference that refers to a value. (½) • If lookup cannot find the lookup value it uses the largest value in the array that is less than or equal to lookup value. (½) • If that value is smaller than the smallest value in the first row or column then it will return N/A error value. (½)

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

Marking-scheme solution

More from Spreadsheet