Special function in Google Sheets

Introduction

Google Sheets offers a variety of special functions that enhance data management and analysis. This article explores the IFERROR, XLOOKUP, OFFSET, SUMPRODUCT, and IMAGE functions, along with techniques for editing links. Mastering these functions improves efficiency and accuracy in handling data.

Why Are Special Functions Important?

Special functions provide advanced capabilities for manipulating data, handling errors, and referencing dynamically, thereby strengthening spreadsheets’ versatility and robustness. They ensure data integrity, enhance calculations, and facilitate the creation of visually appealing reports.

IFERROR Function

The IFERROR function manages formula errors by returning a specified value if an error occurs.

How to Use the IFERROR Function

• Syntax: =IFERROR(value, [value_if_error])
• Example: =IFERROR(A1/B1, “Error”)

The IFERROR function prevents formula errors from disrupting data analysis by providing alternative values or messages.

XLOOKUP Function

The XLOOKUP function retrieves values from a result range based on its match in a lookup range, returning the closest match if none is found.

XLOOKUP for BigQuery

Sample usage

• XLOOKUP(“Apple”, A2:A, E2:E) to replace VLOOKUP(“Apple”, A2:E, 5, FALSE)
• XLOOKUP(“Price”, A1:E1, A6:E6) to replace HLOOKUP(“Price”, A1:E6, 6, FALSE)
• XLOOKUP where match column is to the right of the output column
• XLOOKUP(“Apple”, E2:E7, A2:A7). The VLOOKUP equivalent is VLOOKUP(“Apple”, {E2:E7, A2:A7}, 2, FALSE)

Syntax

XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

• search_key: The value to search for. For example, 42, ‘Cats’ or B24.
• lookup_range: The range to be considered for the search. This range must be a singular row or column.
• result_range: The range to be considered for the result. This range’s row or column size should be the same as the lookup_range, depending on how the look-up is done.
• missing_value: [OPTIONAL – #N/A by default] The value to return if no match is found.
• match_mode: [OPTIONAL – 0 by default] The manner in which to find a match for the search_key.
  • 0 is for an exact match.
  • 1 is for an exact match or the next value that is greater than the search_key.
  • -1 is for an exact match or the next value that is lesser than the search_key.
  • 2 is for a wildcard match.
• search_mode: [OPTIONAL – 1 by default] The manner in which to search through the lookup_range.
  • 1 is to search from the first entry to the last.
  • -1 is to search from the last entry to the first.
  • 2 is to search through the range with binary search. The range needs to be sorted in ascending order first.
  • -2 is to search through the range with binary search. The range needs to be sorted in descending order first.

Notes

If result_range is more than one row or column, then the output will be the entire row/column at the index a match was found in the lookup_range.

Example:

Lookup table for all examples.

XLOOKUP for total amount sold with match_mode and search_mode omitted and missing argument specified.

XLOOKUP for total amount sold with match_mode = 0 and search_mode = 1 and -1.

It is for total amount sold with match_mode = 1 and -1 and search_mode omitted.

XLOOKUP using horizontal matching and returning an entire column.

OFFSET Function

The OFFSET function returns a range offset by a specified number of rows and columns from a starting point.

Sample usage

OFFSET(A2,3,4,2,2)

OFFSET(A2,1,1)

Syntax

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

cell_reference – The starting point from which to count the offset rows and columns.

offset_rows – The number of rows to shift by.offset_rows must be an integer but may be negative. If a decimal value is provided, the decimal part will be truncated.

offset_columns – The number of columns to shift by.offset_columns must be an integer but may be negative. If a decimal value is provided, the decimal part will be truncated.

height – [ OPTIONAL ] – The height of the range to be returned starting at the offset target.

width – [ OPTIONAL ] – The width of the range to be returned starting at the offset target.

The OFFSET function is useful for creating dynamic ranges and references in your formulas, especially when dealing with changing datasets.

SUMPRODUCT Function

The SUMPRODUCT function calculates the sum of products from corresponding entries in arrays or ranges.

Sample usage

• SUMPRODUCT(A2:C5,D2:F5)
• SUMPRODUCT({1,2,3,4},{5,6,7,8})

Syntax

SUMPRODUCT(array1, [array2, …])

array1 – The first array or range whose entries will be multiplied with corresponding entries in the second such array or range.

array2, … – [ OPTIONAL – {1,1,1,…} with same length as array1 by default ] – The second array or range whose entries will be multiplied with corresponding entries in the first such array or range.

SUMPRODUCT is powerful for performing complex calculations that involve multiple criteria, such as weighted averages, conditional sums, and matrix operations.

IMAGE Function

Inserts an image into a cell.

Sample usage

• IMAGE(‘https://www.google.com/images/srpr/logo3w.png’)
• IMAGE(A2,2)
• IMAGE(A2,4,120,200)

Syntax

IMAGE(url, [mode], [height], [width])

url – The URL of the image, including protocol (e.g. http://).

The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

mode – [ OPTIONAL – 1 by default ] – The sizing mode for the image

1 resizes the image to fit inside the cell, maintaining aspect ratio.

2 stretches or compresses the image to fit inside the cell, ignoring aspect ratio.

3 leaves the image at original size, which may cause cropping.

4 allows the specification of a custom size.

Note that no mode causes the cell to be resized to fit the image.

height – [ OPTIONAL ] – The height of the image in pixels. mode must be set to 4 in order to set a custom height.

width – [ OPTIONAL ] – The width of the image in pixels. mode must be set to 4 in order to set a custom width.

Editing Links

Editing links in Google Sheets allows you to create, modify, and manage hyperlinks to external websites, other sheets, or specific cell ranges.

How to Edit Links

1. Select the cell containing the link you want to edit.
2. Click on the Insert menu and choose Link.
3. Modify the URL or text in the link dialog box.
4. Click Apply.

Editing links is essential for maintaining accurate and up-to-date references in your spreadsheets, ensuring users can easily navigate to relevant information.

Conclusion

Special functions like IFERROR, XLOOKUP, OFFSET, SUMPRODUCT, and IMAGE, along with link editing capabilities, significantly enhance Google Sheets’ functionality and usability. Mastering these functions empowers users to handle errors effectively, perform advanced calculations, create dynamic references, embed images, and manage hyperlinks efficiently. Incorporating these tools enhances data accuracy, efficiency, and visual appeal, making data analysis more insightful and powerful.

Frequently Asked Questions