Data Cleaning Spreadsheet Formulas for Google Sheets
Data Cleaning Spreadsheet Formulas 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,
Data Cleaning Tools
process data via the sidebar and can handle larger datasets. Data Cleaning Spreadsheet Formulas have a 30-second execution timeout. See a live walkthrough on the
interactive demo.
Data Cleaning Spreadsheet Formulas vs Data Cleaning Tools
Both Data Cleaning Spreadsheet Formulas and Data Cleaning Tools run the same matching and cleaning engines. They differ in how you use them and how much data they can handle.
| Aspect | Data Cleaning Spreadsheet Formulas | Data Cleaning Tools |
|---|---|---|
| How you use them |
Type a formula in a cell, for example
=FLOOKUP(...)
|
Open the sidebar and click Run |
| Best for | Ad-hoc lookups, spot checks and single-cell results | Bulk processing, large ranges and repeatable workflows |
| Execution window | Up to 30 seconds per call | Up to 6 minutes per run, with resumable chaining |
| Typical use | A quick answer for one or a few values | Cleaning, deduplicating or enriching a whole dataset |
If your range is small or you only need a fast lookup, a Data Cleaning Formula is the right tool. If you are processing a large dataset or building a repeatable workflow, use Data Cleaning Tools.
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 | Extracts unique values by percentage similarity | 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. Results below
0.85
display with a notice.
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. Results below
0.85
display with a notice.
Practical guidance:
| Scenario / Tip | Guidance |
|---|---|
| Result visibility |
Results at
0.85
and above display normally. Lower thresholds find more matches.
0.85.
|
| 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 | A cell or range of words/punctuation to remove. If a single cell is used, items should be separated by spaces. |
| threshold | Optional |
Minimum similarity, from
0
to
1,
for removing items when using the
"text"
operation. Default is
0.75.
|
| operation | Optional |
Type of content to remove. Options are:
"text"
(removes stop words),
"punctuations"
(removes punctuation),
"diacritics"
(default, removes diacritical marks),
"domain"
(extracts the domain from a URL) 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. You could utilise functions like these ones:
"St. John's Cafe".
By default, it removes only diacritics:
=NORMALIZE(A2)
The formula returns
"St Johns Café".
This removes punctuation like 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 to compare against
right_string.
|
| right_string | Required |
The value to compare against
left_string.
|
| 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.
|
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 a close match like "Innovate Corporation", it will return the industry from column E along with the similarity score, for example:
{"Tech", 0.92}.
=FLOOKUP(A2, D2:E100, 1, 2, 0.85, "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. This is perfect for when you need to pull all related information for a matched item from another dataset.
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, like:
{"Widget Professional", "Electronics", "$99.99", 0.95}.
=SHEETMERGE(A2, C2:E100, 1)
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. It is perfect for when you need a quick, clean lookup result and do not need to see the 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 the best match for "Innovate Corp" in column D and return only the corresponding industry from column E, for instance,
"Tech".
=QLOOKUP(A2, D2:E100, 1, 2, 0.85)
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 or horizontal range to search for. For multiple values, utilise a single row, e.g.
A1:E1.
|
| 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
A1:
This formula 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, threshold)
The DEDUPE function provides a powerful and flexible way to remove duplicate rows from a range. Unlike GUI-based tools, it is non-destructive, returning a new, clean array of your data. You can define duplicates based on percentage similarity and 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".
|
| 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 an 85 per cent similarity threshold and returns a new table containing only the unique rows, keeping the first occurrence of each duplicate group.
=DEDUPE(A2:C100, 2, "first", 0.85)
ULIST
=ULIST(col_array, index_num, threshold, "operation")
The ULIST function returns unique values from any list of text entries, based on percentage 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 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 |
Use
by_percentage
to extract by percentage similarity (default). This is the only supported mode.
|
Example of ULIST
Imagine you have a list of cities in column A (
A2:A100),
with slight variations like "New York", "New York City" and "NYC". You wish to create a clean, unique list of these cities. The formula will analyse column A and, based on an 85 per cent similarity threshold, recognise that "New York", "New York City" and "NYC" are variations of the same entity. It will return a clean list with just one representative entry, such as
"New York".
=ULIST(A2:A100, 1, 0.85, "by_percentage")
Possible Error Messages
- !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: Invalid operation. You entered an operation parameter that is not supported. Check that you specified the correct operation ("by_percentage").
- !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 return only the unique values.
When should I use DEDUPE versus ULIST?
Use DEDUPE when you want to return full rows with duplicates removed. Use ULIST when you only need a unique list of values.
Can I use fuzzy matching?
Fuzzy matching is available. View pricing for current plan details. See a live example on the interactive demo.
Why do I see a message about a licence requirement?
Data Cleaning Spreadsheet Formulas are included with paid plans for a limited window: 30 days on Starter, 90 days on Pro and 180 days on Teams. See current plans on the pricing page or try the interactive demo for a live walkthrough.
What should I do if a Data Cleaning Spreadsheet Formula times out?
Reduce the range size, simplify parameters or move larger jobs to Data Cleaning Automation for recurring processing.