REFERENCE GUIDE TO CUSTOM FUNCTIONS
Quick Overview
| Function | What it does | What it is best for |
|---|---|---|
| NORMALIZE | Cleans and standardizes text entries | Preparing data for analysis |
| FUZZYSIM | Compares two text values for similarity | Spotting near-duplicates |
| FLOOKUP | Finds the closest match in a table | Reconciling data by token variations |
| SOUNDMATCH | Matches words by how they sound | Reconciling data by phonetic variations |
| ULIST | Extracts unique values by similarity or sound | Enhanced deduplication |
Each Flookup function is designed to help you clean, match and analyze your spreadsheet data efficiently in Google Sheets.
How to Skip Optional Parameters
When using Flookup functions, you can skip optional parameters by leaving them blank, but you must still include the correct number of commas.
For example, if a function has four parameters and you want to skip the third, use two commas with nothing between them:
=FUNCTION(required1, required2, , required4)
If you want to skip all optionals at the end, you can simply omit them:
=FUNCTION(required1, required2)
NORMALIZE
=NORMALIZE(lookup_value, stop_array, threshold, "operation")
The NORMALIZE function for Google Sheets removes unwanted words, diacritics and punctuation from spreadsheet text entries. This process helps clean your data and improves the accuracy of fuzzy matching and data analysis.
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required | The cell or range containing the values to normalize. For example: A1 or A1:A100. |
| stop_array | Optional | Words or punctuation to remove, separated by commas, in a single cell. |
| threshold | Optional | Minimum similarity, from 0 to 1, for removing items from lookup_value. Default is 1, which means only exact matches are removed. |
| operation | Optional | Type of content to remove. Options: text removes stop words, punctuations removes punctuation, diacritics removes diacritical marks (default), domain extracts the domain from a URL, path extracts the path from a URL. |
If you do not specify the operation, diacritics are removed by default. For example: =NORMALIZE(A5).
FUZZYSIM
=FUZZYSIM(left_string, right_string)
The FUZZYSIM function calculates the percentage similarity between two text entries and returns a decimal value. This is useful for fuzzy matching and data comparison in Google Sheets.
Parameters
| Parameter | Type | Description |
|---|---|---|
| left_string | Required | The main text value to compare against right_string. |
| right_string | Required | The value to compare against left_string. |
Please Note: You can compare single cells or ranges. For example: A1 vs B1, A1 vs B1:B100 or A1:A100 vs B1:B100.
FLOOKUP
=FLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold, merge_col)
The FLOOKUP function searches a specified column for matches and returns a value from another column, along with the percentage similarity. FLOOKUP is ideal for reconciling and merging spreadsheet data in Google Sheets.
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required | Values to search for. You can use a single cell or a range, such as A1 or A1:A500. |
| table_array | Required | The range that contains the data to compare and return, for example B1:E500. |
| lookup_col | Required | The index of the column in table_array to search for matches. Use 1 for the first column, 2 for the second and so on. Value must be greater than 0. |
| index_num | Required | The index of the column in table_array from which to return values. Use 1 for the first column, and so on. Value must be greater than 0. |
| threshold | Optional | Minimum similarity, from 0 to 1, for a match. Default is 0.85. |
| merge_col | Optional | Set to "true" to return all columns in the matching row and ignore index_num. Set to "false" (default) to return only the column specified by index_num. |
QLOOKUP
=QLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold, "operation")
Returns only the lookup result, not the similarity. The operation parameter lets you choose the matching algorithm i.e. by_set for subtle differences or by_union for regular differences.
HFLOOKUP
=HFLOOKUP(lookup_value, table_array, lookup_row, index_num, threshold)
Like a transposed FLOOKUP for horizontally oriented datasets. Returns only the match, not the similarity.
SOUNDMATCH
=SOUNDMATCH(lookup_value, table_array, lookup_col, index_num)
The SOUNDMATCH function looks up values in a specified column and returns matches based on a refined version of the Soundex algorithm. This is useful for phonetic matching and cleaning inconsistent data in Google Sheets.
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required | Values to search for. You can use text, a cell reference or a range. |
| table_array | Required | The range that contains the data to compare and return. |
| lookup_col | Required | The index of the column in table_array to search for matches. Use 1 for the first column and so on. Value must be greater than 0. |
| index_num | Required | The index of the column in table_array from which to return values. Use 1 for the first column, and so on. Value must be greater than 0. |
ULIST
=ULIST(col_array, index_num, threshold, "operation")
The ULIST function returns unique values from any list of text entries, based on percentage or sound similarity. If you like the UNIQUE function in Google Sheets, you will find this function even more flexible for deduplication and data cleaning.
Parameters
| Parameter | Type | Description |
|---|---|---|
| col_array | Required | The range of one or more columns from which to extract unique values. For example: A1:C200. |
| index_num | Optional | The index of the column in col_array to analyze for duplicates. Default is 1, meaning that the first column is processed. |
| threshold | Optional | Minimum similarity, from 0 to 1, for uniqueness. Default is 0.85. |
| operation | Optional | Choose by_percentage to extract by percentage similarity (default) or by_sound to extract by sound similarity. |
Possible Error Messages
- !ERROR: Invalid index value. The index number is less than 1 or greater than the number of columns in the search table.
- !NOTICE: No match found. No matches are available for the parameters you set.
- !ERROR: Invalid threshold value. The value is less than 0 or greater than 1.
- !ERROR: Invalid operation. You entered an operation that is not supported by this function.
Explore Enhanced Menu Functions
Menu functions are available under Extensions > Flookup Data Wrangler in your spreadsheet menu. These functions have a 6-minute timeout (compared to 30 seconds for custom functions), so you can clean more data at once. They also support advanced operations: