VLOOKUP
Description: Searches for a value in the first column of a range and returns a value in the same row from a specified column.
Syntax: =VLOOKUP(search_key, range, index, [is_sorted])
- search_key: The value to search for.
- range: The range to search within. The first column is where the search key is looked up.
- index: The column number in the range from which to retrieve the value.
- is_sorted: [Optional] Indicates if the range is sorted. Use FALSE for an exact match.
Example:
=VLOOKUP(A2, B2:D10, 3, FALSE)
HLOOKUP
Description: Searches for a value in the first row of a range and returns a value in the same column from a specified row.
Syntax: =HLOOKUP(search_key, range, index, [is_sorted])
- search_key: The value to search for.
- range: The range to search within. The first row is where the search key is looked up.
- index: The row number in the range from which to retrieve the value.
- is_sorted: [Optional] Indicates if the range is sorted. Use FALSE for an exact match.
Example:
=HLOOKUP(A1, B1:D10, 3, FALSE)
SUMIF
Description: Adds the values in a range that meet a single condition.
Syntax: =SUMIF(range, criterion, [sum_range])
- range: The range to evaluate.
- criterion: The condition to meet.
- sum_range: [Optional] The actual cells to sum. If omitted, the cells in the range are summed.
Example:
=SUMIF(A1:A10, ">5", B1:B10)
SUMIFS
Description: Adds the values in a range that meet multiple conditions.
Syntax: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- sum_range: The actual cells to sum.
- criteria_range1: The range to evaluate for the first condition.
- criterion1: The first condition to meet.
- criteria_range2, criterion2, ...: [Optional] Additional ranges and conditions.
Example:
=SUMIFS(B1:B10, A1:A10, ">5", C1:C10, "<10")
COUNTIF
Description: Counts the number of cells that meet a single condition.
Syntax: =COUNTIF(range, criterion)
- range: The range to evaluate.
- criterion: The condition to meet.
Example:
=COUNTIF(A1:A10, ">5")
COUNTIFS
Description: Counts the number of cells that meet multiple conditions.
Syntax: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- criteria_range1: The range to evaluate for the first condition.
- criterion1: The first condition to meet.
- criteria_range2, criterion2, ...: [Optional] Additional ranges and conditions.
Example:
=COUNTIFS(A1:A10, ">5", B1:B10, "<10")
CHOOSE
Description: Returns a value from a list of values based on an index number. Useful for scenario modelling.
Syntax: =CHOOSE(index, value1, [value2, ...])
- index: The position of the value to return.
- value1, value2, ...: The list of values to choose from.
Example:
=CHOOSE(2, "Red", "Blue", "Green")
INDEX
Description: Returns the value of a cell in a given range based on row and column numbers.
Syntax: =INDEX(range, row, [column])
- range: The range to search within.
- row: The row number to look in.
- column: [Optional] The column number to look in.
Example:
=INDEX(A1:C10, 2, 3)
MATCH
Description: Searches for a value in a range and returns the relative position of that item.
Syntax: =MATCH(search_key, range, [match_type])
- search_key: The value to search for.
- range: The range to search within.
- match_type: [Optional] The match type: 1 for less than, 0 for exact match, -1 for greater than.
Example:
=MATCH("Apple", A1:A10, 0)
Combination of INDEX & MATCH
Description: More robust than VLOOKUP or HLOOKUP as it doesn't require the lookup column to be the first column and can work with both rows and columns.
Syntax: =INDEX(result_range, MATCH(search_key, search_range, 0))
- result_range: The range to return a value from.
- search_key: The value to search for.
- search_range: The range to search within for the search_key.
Example:
=INDEX(B1:B10, MATCH("Apple", A1:A10, 0))
OFFSET
Description: Returns a reference to a range that is offset from a starting cell by a specified number of rows and columns.
Syntax: =OFFSET(cell_reference, rows, cols, [height], [width])
- cell_reference: The starting point.
- rows: The number of rows to offset by.
- cols: The number of columns to offset by.
- height: [Optional] The height of the returned range.
- width: [Optional] The width of the returned range.
Example:
=OFFSET(A1, 2, 3)
VARIABLES TO TEST WITHIN IF CONDITIONS
NESTED IF STATEMENTS
Description: Multiple IF statements can be nested together to handle multiple conditions.
Syntax: =IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false2))
Example:
=IF(A1 > 10, "Greater", IF(A1 > 5, "Between 5 and 10", "Less than 5"))
OR
Description: Returns TRUE if any of the conditions are TRUE.
Syntax: =IF(OR(condition1, condition2, ...), value_if_true, value_if_false)
Example:
=IF(OR(A1 > 10, B1 < 5), "Yes", "No")
AND
Description: Returns TRUE if all the conditions are TRUE.
Syntax: =IF(AND(condition1, condition2, ...), value_if_true, value_if_false)
Example:
=IF(AND(A1 > 10, B1 < 5), "Yes", "No")
Worked Examples: