HOW TO USE SPREADSHEET FUNCTIONS

Introduction to Spreadsheet Functions

These functions can be accessed or activated by typing specific formulas directly inside any spreadsheet cell.

They offer a significant advantage as they seamlessly integrate into your existing workflow. They are particularly useful for routine data cleaning tasks, enabling you to efficiently automate the same. Moreover, spreadsheet functions are universally accessible, whether you are using a phone, tablet, or PC ensuring that you work effectively on any platform.

NORMALIZE

=NORMALIZE(operation, lookup_value, stop_array, threshold)

Use the NORMALIZE function to remove unwanted words, diacritics and punctuation marks from text entries. This improves the data cleaning process by removing parts of text entries or whole text entries that could negatively affect the accuracy of the fuzzy matching process. Moreover, with less data to process, the speed of the data cleaning process could be greatly improved.

NORMALIZE Parameters

Be careful not to mix unwanted words with punctuation marks in your "stop_array" input.

FUZZYMATCH

=FUZZYMATCH(left_string, right_string)

Use the FUZZYMATCH function to calculate the percentage similarity between text entries and return the result in decimal form. 

You can compare single cells or ranges to one another e.g. A1 vs B1, A1 vs B1:B100 or A1:A100 vs B1:B100 but "left_string" should never hold more rows of data than "right_string".

FUZZYMATCH Parameters

FLOOKUP

=FLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold, rank)

Use the FLOOKUP function to search any column of a range of cells, and then return a value from a specified column of that range, paired with its respective percentage similarity. 

FLOOKUP Parameters

Derivatives of FLOOKUP

There are two derivatives of the FLOOKUP function that are designed to cater for special lookup and merge cases that the former does not do. Below is an overview of how they can be helpful:

QLOOKUP

=QLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold)

The QLOOKUP function is also works like FLOOKUP, but it will only return the lookup result without the relative percentage similarity. It also only returns the best match in the dataset, therefore, has no option to return other matches for each lookup item.

MLOOKUP

=MLOOKUP(lookup_value, table_array, lookup_col, threshold, rank)

This function works exactly like FLOOKUP, but with one difference:

Whereas FLOOKUP returns a matched value from a cell, MLOOKUP will instead return the entire row of "table_array" in which the match is located. This makes it very useful in merging different lists or tables with multiple data points.

Therefore, when using MLOOKUP, you are advised to leave an appropriate number of columns for all the results to be displayed without overriding any of your pre-existing data.

SOUNDMATCH

=SOUNDMATCH(lookup_value, table_array, lookup_col, index_num, rank)

Use the SOUNDMATCH function to lookup values in any column and return values based on a refined version of the Soundex algorithm.

SOUNDMATCH Parameters

ULIST

=ULIST(col_array, index_num, threshold)

You can use the ULIST function to return unique values from any list of text entries based on their relative percentage similarities. If you like the UNIQUE function in Google Sheets, you will love this function.

ULIST Parameters

Error Messages Explained