HOW TO USE FORMULA-BASED FUNCTIONS
Introduction to Formula-based 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, formula-based functions are universally accessible, whether you are using a phone, tablet, or PC ensuring that you work effectively on any platform.
NORMALIZE
=NORMALIZE(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
lookup_value [Required]. This refers to the text entries that are to be normalized. It can be a reference to one cell e.g. A1 or a range of multiple cells in a single column e.g. A1:A100.
stop_array [Optional]. This refers to the values that you wish to eliminate from the "lookup_value" entry. It could comprise a group of unwanted words (separated by commas) or punctuation marks (without any delimiters). These values should be contained within a single cell.
Be careful not to mix unwanted words with punctuation marks in your "stop_array" input.
threshold [Optional]. This refers to the minimum percentage similarity between parts of the "lookup_value" that you would like to remove and the values in the "stop_array". The default value is 1, which means that only exact matches will be removed from the "lookup_value" entry.
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
left_string [Required]. The primary text entry to be compared to "left_string".
right_string [Required]. The secondary text entry to be compared to "right_string".
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
lookup_value [Required]. This determines the specific value or values you are looking for within any column of the "table_array". The "lookup_value" can be a reference to one cell e.g. A1 or a range covering multiple rows e.g. A1:A500.
table_array [Required]. This determines the range of cells e.g. B1:E500 that contains the data to be returned or merged. The values in the specified or target column of "table_array" are methodically compared to the values in "lookup_value". These values can be text, numbers, or any other type of data.
lookup_col [Required]. This determines the index of the "table_array" column that this function will search through. A "lookup_col" value of 1 means the function will search the first column in "table_array", a value of 2 will search the second column, and so on. The values must always be greater than 0.
index_num [Required]. This determines the column index in the "table_array" from which data should be returned. If the "index_num" is 1, the value from the first column in the "table_array" is returned. If it is 2, the value from the second column is returned, and so on. The values must always be greater than 0.
threshold [Optional]. This parameter determines whether FLOOKUP should return exact or approximate matches. It represents the similarity level below which results will not be returned from "table_array". Valid values range from 0 (for no filtering) to 1 (for exact matches). The default value is set at 0.6.
rank [Optional]. This determines the rank of the potential matches that you want to be returned from "table_array". The values can range from 1, which corresponds to the first possible match, to n, which corresponds to the nth possible match. The default value is 1, and the results are ranked in descending order based on their percentage similarity.
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.
Therefore, when using MLOOKUP, please leave enough columns for all the data to be displayed without overriding any pre-existing data.
SOUNDMATCH
=SOUNDMATCH(lookup_value, table_array, lookup_col, index_num, rank)
Use the SOUNDMATCH function to lookup and return values based on their Soundex codes.
SOUNDMATCH also allows you to return alternative matches using the optional "rank" parameter. These matches range from 1 (best match) to n (the nth best match).
Please note that SOUNDMATCH parameters work in much the same way as the FLOOKUP parameters. The only differences are that SOUNDMATCH does not include or use the "threshold" parameter and it sorts results alphabetically instead of sorting them by percentage similarity.
ULIST
=ULIST(col_array, index_num, threshold)
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, you will love this function.
ULIST Parameters
col_array [Required]. This a range of one or more columns from which you intend to extract unique text entries and discard duplicates e.g. A1:C200.
index_num [Optional]. This is an index number that represents the column in "col_array" that is going to be analysed for duplicates with the view of extracting unique values. If no input is made, the first column of "col_array" will be analysed.
threshold [Optional]. This variable determines whether ULIST should extract exact or approximate matches. It represents the relative percentage similarity between any two text entries in the target column of "col_array", below which text entries are considered unique and above which text entries are considered to be duplicates. The default value is set at 0.6.
Error Messages Explained
!FunctionUnavailable: You have not activated any of our revised Flookup plans.
!SubscriptionInactive: You have not activated Flookup or you are using the wrong user account.
!IndexError: You have entered and index number that is less than 1 or is larger than the number of columns available in "tableArray".
!RankError: You have entered a rank figure that is less than 1.
!NoMatchFound: There are no matches available for the current parameters you have set.
!ThresholdError: You have entered a figure that is less than 0 or greater than 1.