Data Cleaning Spreadsheet Formulas for Google Sheets
Data Cleaning Spreadsheet Formulas in Flookup Data Wrangler are formulas you type directly into your cells (sometimes called custom functions), like
=FLOOKUP
or
=NORMALIZE.
They return results immediately in the cell where you enter them, making them ideal for ad-hoc lookups and spot checks on smaller ranges. By contrast,
Standard Data Cleaning
process data via the sidebar and can handle larger datasets. Data Cleaning Spreadsheet Formulas use internal processing guards of approximately 22 or 28 seconds, depending on the function. See a live walkthrough on the
demo spreadsheet.
Quick Overview
| Function | What it does | What it is best for |
|---|---|---|
| NORMALIZE | Cleans and normalises text entries | Preparing data for analysis |
| FUZZYSIM | Compares two text values for percentage similarity | Spotting near-duplicates between words or sentences |
| FLOOKUP | Finds the closest match in a table by percentage similarity and returns the result plus its score or row position | Reconciling data by token variations |
| SHEETMERGE | Finds the best match and returns the entire row in which the match is found | Enriching data by finding full matching records |
| QLOOKUP | Finds the closest match in a table by percentage similarity and returns the result alone | Reconciling data by token variations |
| HFLOOKUP | Finds the closest match in a row by percentage similarity and returns the result alone | Reconciling data organised horizontally |
| DEDUPE | Removes duplicate rows by percentage similarity | Removing duplicates using a non-destructive method |
| ULIST | Returns complete unique rows based on similarity in one column | Enhanced deduplication |
TIP 1:
When using these functions, utilise ranges as inputs, e.g.
A1:A100,
instead of single cells, e.g.
A1,
for efficient data processing.
TIP 2:
You can set any threshold from
0
to
1.
Choosing a similarity threshold
A similarity threshold controls how broadly the functions match variants. Lower thresholds widen the net and increase recall: they will find more distant variants but also include more false positives. Higher thresholds increase precision: they return only close matches and reduce the need for manual review. You can set any threshold from
0
to
1.
Practical guidance:
| Scenario / Tip | Guidance |
|---|---|
| Result visibility | Results at or above the selected threshold display normally. Lower thresholds find more matches. |
| Exploratory deduplication |
When data is heavily inconsistent, try a lower threshold (for example
0.65
to
0.75)
and inspect the candidate matches.
|
| Routine reconciliation |
For names or addresses, begin at
0.75
and only lower the threshold if too few useful matches are returned.
|
| Sample testing |
Use
FUZZYSIM
on a small sample to observe typical scores before applying a threshold across a dataset.
|
| Pre-processing |
Apply
NORMALIZE
first (remove punctuation and diacritics) so thresholds measure substantive differences rather than incidental noise.
|
| Iterative refinement | Test on a representative sample, review the results and refine the threshold until it fits your accuracy Vs coverage requirements. |
Users can set any threshold and use all text-cleaning operations. View pricing for details.
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 wish to skip the third, use two commas with nothing between them:
=FUNCTION(required1, required2, , required4)
If you wish to skip all optional parameters 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 normalise. For example:
A1
or
A1:A100.
|
| stop_array | Optional |
Items to remove. For the
"text"
operation, enter words as a comma-separated list, for example
"the,and,of". For the
"punctuations"
operation, enter each punctuation mark, for example
".,'".
|
| threshold | Optional |
Minimum similarity, from
0
to
1,
for removing words when using the
"text"
operation. Default is
0.75.
|
| operation | Optional |
Type of content to process. Options are:
"text"
(removes stop words),
"punctuations"
(removes the punctuation marks supplied in
stop_array),
"diacritics"
(default, removes diacritical marks),
"domain"
(extracts the registrable domain, meaning the public suffix and its preceding domain label) and
"path"
(extracts the path from a URL).
|
Example of NORMALIZE
Suppose cell
A2
contains the text
"St. John's Café"
and you wish to clean it for analysis.
"St. John's Cafe".
By default, it removes only diacritics:
=NORMALIZE(A2)
The formula returns
"St Johns Café".
This removes the period and apostrophe:
=NORMALIZE(A2, ".'", , "punctuations")
FUZZYSIM
=FUZZYSIM(left_string, right_string, "compare_mode")
The FUZZYSIM function calculates the percentage similarity between two text entries using one of two advanced algorithms. This is useful for fuzzy matching and data comparison in Google Sheets.
Parameters
| Parameter | Type | Description |
|---|---|---|
| left_string | Required |
The main text value or range to compare against
right_string.
|
| right_string | Required |
The value or range to compare against
left_string.
If both arguments are ranges, the function compares corresponding values up to the shorter range. If only one argument is a range, it compares the single value with every cell in that range.
|
| compare_mode | Optional |
The comparison method to use.
"by_word"
(default) compares strings based on character pairs.
"by_phrase"
compares strings word-by-word, which is better for matching phrases with different word orders or minor variations. Any other value falls back to
"by_word".
|
Example of FUZZYSIM
Imagine you have
"Apple Inc."
in cell
A2
and
"Apple Incorporated"
in cell
B2.
0.3.
The shared tokens contribute overlap while the longer second string keeps the score low, so values this size usually warrant a closer look:
=FUZZYSIM(A2, B2)
To compare phrases with different word orders, use the
"by_phrase"
mode. For example, if cell
A3
contains
"big red car"
and cell
B3
contains
"this red car",
the following formula will return
0.63
because they are very similar despite the word order:
=FUZZYSIM(A3, B3, "by_phrase")
FLOOKUP
=FLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold, output_mode)
The FLOOKUP function searches a specified column for the best match and returns the corresponding value from your chosen return column. By default, it pairs the result with its similarity score, but you can also choose to see the row number of the match instead. It is ideal for detailed data reconciliation where you need to see the confidence or position of each match.
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required |
Values to search for in
table_array.
You can utilise 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.75.
|
| output_mode | Optional |
Determines what the result is paired with. Use
"score"
(default) to return the similarity score or
"position"
to return the 1-based row number of the match in
table_array.
|
Example of FLOOKUP
Let us say you have a list of company names in column A and a master list of companies and their industries in columns D and E. You wish to find the industry for "Innovate Corp" (in cell
A2)
from your master list (D2:E100).
The company names are in column D (the 1st column of the table array) and industries in column E (the 2nd).
The following formula looks for "Innovate Corp" in column D.
If it finds "Innovate Corporation" as the best match, it will return the industry from column E along with the similarity score:
{"Tech", 0.48}.
=FLOOKUP(A2, D2:E100, 1, 2, 0.45, "score")
SHEETMERGE
=SHEETMERGE(lookup_value, table_array, lookup_col, threshold, num_cols)
The SHEETMERGE function is designed for data enrichment. It finds the best fuzzy match for a lookup value in a table and returns the entire matching row, with the similarity score appended as the last column. Use it to return all columns from a matched record.
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required |
Values to search for in
table_array.
You can utilise a single cell or a range e.g.
A1
or
A1:A200.
|
| table_array | Required |
The range that contains the data to compare with
lookup_value
data and return, for example
B1:C200.
|
| 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.
|
| threshold | Optional |
Minimum similarity for a match. Defaults to
0.75.
|
| num_cols | Optional | The number of columns to return from the start of the matching row. If omitted, all columns from the row are returned. |
Example of SHEETMERGE
Suppose you have a simple list of product names in column A and a detailed product database in columns
C2:E100
(Name, Category, Price). You wish to pull all details for the product in
A2,
which is "Widget Pro". The formula below will search for "Widget Pro" in the first column of
C2:E100.
If it finds "Widget Professional" as the best match, it will return the entire row for that product, plus the similarity score in a new last column:
{"Widget Professional", "Electronics", "$99.99", 0.25}.
The threshold must be at or below that score.
=SHEETMERGE(A2, C2:E100, 1, 0.2)
QLOOKUP
The QLOOKUP function is a streamlined version of FLOOKUP. It searches a column for the best match and returns a single corresponding value without the similarity score.
=QLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold)
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required |
Values to search for in
table_array.
You can utilise a single cell or a range e.g.
A1
or
A1:A200.
|
| table_array | Required |
The range that contains the data to compare with
lookup_value
data and return, for example
B1:C200.
|
| lookup_col | Required |
The index of the column in
table_array
from which to return values. Use
1
for the first column and so on. It must be greater than
0.
|
| index_num | Required |
The column number in
table_array
to return data from.
|
| threshold | Optional |
Minimum similarity for a match. Defaults to
0.75.
|
Example of QLOOKUP
Using the same scenario as
FLOOKUP,
you wish to find the industry for "Innovate Corp" (in
A2)
from your master list in
D2:E100,
but you only need the industry name, not the score. This formula will find "Innovate Corporation" as the best match for "Innovate Corp" in column D and return only the corresponding industry from column E, for instance,
"Tech".
The threshold must be at or below the similarity score of that pair.
=QLOOKUP(A2, D2:E100, 1, 2, 0.45)
HFLOOKUP
The HFLOOKUP function works like a transposed version of QLOOKUP. It searches a row for the best match and returns a single corresponding value from another row without the similarity score. It is ideal for horizontal lookups where your data is organised in rows instead of columns.
=HFLOOKUP(lookup_value, table_array, lookup_row, index_num, threshold)
Parameters
| Parameter | Type | Description |
|---|---|---|
| lookup_value | Required |
The value to search for. To compare several values, pass a single horizontal range such as
A1:E1.
Do not pass a vertical range.
|
| table_array | Required |
The range that contains the data to compare with
lookup_value
data and return, for example
A2:E10.
|
| lookup_row | Required |
The row number in
table_array
to search for matches. Use
1
for the first row,
2
for the second and so on. It must be greater than
0.
|
| index_num | Required |
The row number in
table_array
to return data from.
|
| threshold | Optional |
Minimum similarity for a match. Defaults to
0.75.
|
Example of HFLOOKUP
Imagine your data is laid out horizontally. Row 1 (A1:E1)
contains product codes you wish to look up. Your product data is in
A2:E10,
with product codes in row 2 and prices in row 4. To find the price for the code in cell
A1, use the following formula. It searches for the value of
A1
in the first row of the table (which is row 2 of the sheet) and returns the corresponding value from the third row of the table (row 4 of the sheet).
=HFLOOKUP(A1, A2:E10, 1, 3)
DEDUPE
=DEDUPE(data_range, key_column, keep_mode, match_type, threshold)
The DEDUPE function removes duplicate rows from a range without changing the source data. It returns a new array and compares rows using percentage similarity. You can choose whether to keep the first or last instance of a duplicate group.
Parameters
| Parameter | Type | Description |
|---|---|---|
| data_range | Required |
The range of data to process, for example
A1:C100.
|
| key_column | Optional |
The column number within
data_range
to check for duplicates. Defaults to
1.
|
| keep_mode | Optional |
"first"
to keep the first occurrence of a duplicate,
"last"
to keep the last. Defaults to
"first".
|
| match_type | Optional | A compatibility argument that is not used. Leave it blank. |
| threshold | Optional |
The similarity threshold. Defaults to
0.75.
|
Example of DEDUPE
You have a dataset in
A2:C100
where column B contains company names that might be duplicates, e.g. "Global Tech" and "Global Tech, Inc.". You wish to remove these fuzzy duplicates, keeping only the first entry found for each. This formula processes the range, identifies duplicates in the second column based on a 50 per cent similarity threshold and returns a new table containing only the unique rows, keeping the first occurrence of each duplicate group. The blank fourth argument is required before the threshold.
=DEDUPE(A2:C100, 2, "first", , 0.5)
ULIST
=ULIST(col_array, index_num, threshold, "operation")
The ULIST function returns complete unique rows from a range, based on percentage similarity in the selected column. It compares each row with rows already returned, then returns the entire retained row.
Parameters
| Parameter | Type | Description |
|---|---|---|
| col_array | Required |
The range containing the complete rows to return. Every column in a retained row appears in the output.
For example: A1:C200.
|
| index_num | Optional |
The index of the column in
col_array
to analyse 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.75.
|
| operation | Optional | A compatibility argument that is ignored. The function always uses percentage similarity. |
Example of ULIST
Suppose
A2:C100
contains city and country columns, with "New York" followed by "New York City". The formula analyses the first column and uses a 40 per cent similarity threshold. It returns the complete first row when the two city names meet that threshold.
=ULIST(A2:C100, 1, 0.4)
Possible Error Messages
A result of
!Error: Timed out
means that processing did not finish. Reduce the range size, pass fewer lookup values per call and ensure that the cells required for an array result are empty. If the result would overwrite existing cell contents, place the formula where the full output can spill into empty adjacent cells. Use
Data Cleaning Automation
for large or recurring jobs.
- !Notice: No match found. Try lowering the threshold value. No results matching your criteria were found. Try lowering the threshold value to find more similar matches.
- !Notice: No unique rows found. Try lowering the threshold. The ULIST function found no unique rows. Try lowering the threshold to allow more variation in uniqueness.
- !Error: Timed out The function reached its internal time limit. Reduce the range or split the input across several calls.
- !Error: Empty range. The data range passed to DEDUPE is empty. Ensure that your data range contains rows to deduplicate.
- !Error: Empty table range. The table range passed to SHEETMERGE is empty. Ensure that your table range contains data.
Frequently Asked Questions
How do I do a fuzzy match in Google Sheets?
Google Sheets has no built-in fuzzy match formula. Use FLOOKUP to find the best match in a table column and return a value with its similarity score or FUZZYSIM to compare two values directly.
Does Google Sheets have a fuzzy lookup function?
Google Sheets has no native fuzzy lookup function. Flookup adds one: FLOOKUP searches a column for the closest match by percentage similarity and returns the matching value or QLOOKUP for the result without the score.
What is the difference between FLOOKUP and VLOOKUP?
VLOOKUP matches values exactly or to the nearest sorted value. FLOOKUP matches by percentage similarity, so it finds close matches despite typos, abbreviations and spelling variations. FLOOKUP also returns a similarity score for every match.
How do I remove similar duplicates in Google Sheets?
Google Sheets' Remove duplicates tool only catches exact duplicates. Flookup's DEDUPE formula removes rows that are similar by percentage, catching variants like "Global Tech" and "Global Tech Inc". Use ULIST to compare one column and return each complete unique row.
When should I use DEDUPE versus ULIST?
Use DEDUPE when you want to return full rows with duplicates removed. Use ULIST when you want to compare one column for similarity but return each complete unique row.
Can I use fuzzy matching?
Fuzzy matching is available. View pricing for current plan details. See a live example on the demo spreadsheet.
Why do I see a message about a licence requirement?
Data Cleaning Spreadsheet Formulas are included on every plan, for a fixed number of days from the purchase date: 30 days on Sheets Monthly and one year on Sheets Yearly. The window ends and the formulas are blocked, even if the subscription is still active. See current plans on the pricing page or try the demo spreadsheet for a live walkthrough.
What should I do if a Data Cleaning Spreadsheet Formula times out?
Reduce the range size, pass fewer values per call and ensure that all cells required for an array result are empty. If the output would overwrite existing cells, move the formula or clear the required cells. Use Data Cleaning Automation for larger or recurring jobs.