# TUTORIAL

## FUZZYMATCH SYNTAX

`=FUZZYMATCH(string1, string2) `

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

### FUZZYMATCH Parameters

**string1**[Required]. The string to be compared to string2.**string2**[Required]. The string to be compared to string1.

### NOTES:

- The two strings can be interchanged without affecting the result.
- Numbers are treated as text.
- FUZZYMATCH supports multiple languages.

## FLOOKUP SYNTAX

`=FLOOKUP(lookupValue, tableArray, lookupCol, indexNum, [threshold], [rank])`

Use the FLOOKUP function to search the any column of a range of cells, and then return a value from a specified column of that range. For example, suppose you have two separate lists of names contained in ranges **A2:A5** and** B2:D5**. In order to compare the text in both these columns you can simply type:

`=FLOOKUP(A2, B2:D5, 1, 3)`

This formula searches for the value found in cell** A2 **in the first column of the range** B2:D5**, and then** **returns the first match contained in the third column of that same range.

In order to retrieve a value and there is a chance that it might be spelled differently in **B2:D5**, one can type:

`=FLOOKUP(A2, B2:D5, 1, 3, 0.5)`

This formula searches for the value found in** **cell** A2 **in the first column** **of the range **B2:D5** but, unlike the first example, this time the algorithm matches the first string with a 0.5** **percentage similarity minimum. This parameter is adjustable and it takes any value from 0 to 1.

Finally, unlike other solutions, Flookup is not limited to searching the left most column of a specified range. For example, one can simply search for **A2** in the second column of **B2:D5** and return data from the first column by using the formula below**:**

`=FLOOKUP(A2, B2:D5, 2, 1)`

... or search for **A2** in the second column of **B2:D5** and return data from the third column like this:

`=FLOOKUP(A2, B2:D5, 2, 3)`

### FLOOKUP Parameters

**lookupValue**[Required]. The value to search for in the**tableArray**[Required]. The range of cells (database) that contains the data. The values in the user-specified column of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.**lookupCol**[Required]. The column number in tableArray which must be searched by the algorithm. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.**indexNum**[Required]. The column number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first column in tableArray, an indexNum of 2 returns the value in the second column in tableArray, and so on. Values must be greater than 0.**threshold**[Optional]. A variable that specifies whether you want**rank**[Optional]. A variable that specifies the rank, of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to*n*(the*nth*possible match). The default value is 1 and results are ranked in order of descending percentage similarity.

**Tip:** To help FLOOKUP work faster, start by setting *threshold* to 1. After getting all possible exact matches, reduce *threshold* for the new (and hopefully smaller) data set to sort out the more difficult matches.

## HFLOOKUP SYNTAX

`=HFLOOKUP(lookupValue, tableArray, lookupRow, indexNum, [threshold], [rank])`

Use the HFLOOKUP function to search the any row of a range of cells, and then return a value from a specified row of that range. It works exactly like a transposed FLOOKUP function.

### HFLOOKUP Parameters

**lookupValue**[Required]. The value to search for in the**tableArray**[Required]. The range of cells that you want to retrieve data from. The values in a user-specified row of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.**lookupRow**[Required]. The row number in tableArray which must be searched by the algorithm. A lookupRow value of 1 searches the first row in tableArray, a lookupRow value of 2 searches second row in tableArray, and so on. Values must be greater than 0.**indexNum**[Required]. The row number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first row in tableArray, an indexNum of 2 returns the value in the second row in tableArray, and so on. Values must be greater than 0.**threshold**[Optional]. A variable that specifies whether you want**rank**[Optional]. A variable that specifies the rank, of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to*n*(the*nth*possible match). The default value is 1 and results are ranked in order of descending percentage similarity.

**Tip:** To help HFLOOKUP work faster, start by setting *threshold* to 1. After getting all possible exact matches, reduce *threshold* for the new (and hopefully smaller) data set to sort out the more difficult matches.

## SOUNDMATCH SYNTAX

`=SOUNDMATCH(lookupValue, tableArray, lookupCol, indexNum, [rank])`

Use the SOUNDMATCH function to perform lookup tasks and return values based on what their Refined Soundex codes are.

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. It encodes similar sounding words so that they can be matched regardless of differences in spelling. You can find out more about it **here**.

SOUNDMATCH also allows you to return alternative matches using the rank parameter. These matches range from 1 (best match) to *n* (the *nth* best match).

### SOUNDMATCH Parameters

**lookupValue**[Required]. The value to search in the any column of the table or range. The lookupValue argument can be a value or a reference.**tableArray**[Required]. The range of cells (database) that contains the data. You can use a reference to a range. The values in the first column of tableArray are the values searched by lookupValue. These values can be text, numbers, or other values.**lookupCol**[Required]. The column number in tableArray which must be searched by the algorithm. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.**indexNum**[Required]. The column number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first column in tableArray, an indexNum of 2 returns the value in the second column in tableArray, and so on. Values must be greater than 0.**rank**[Optional]. A variable that specifies the rank, of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to*n*(the*nth*possible match). The default value is 1 and

## LATINIZE SYNTAX

`=LATINIZE(str)`

Use the LATINIZE function to remove diacritical marks from strings before applying FUZZYMATCH or any of the lookup functions to them.

The **str** parameter can either be:

- A single cell e.g.
`LATINIZE(A1)`

or, - A range of cells e.g.
`LATINIZE(A1:A20)`

.

## ULIST SYNTAX

`=ULIST(colArray, [indexNum], [threshold]) `

Use ULIST to remove duplicates return unique values from a range that you have specified. This function does not modify the original range or values.

### ULIST parameters

**colArray**[Required]. The range from which you want to return unique values.**indexNum**[Optional]. The column index to analyse for unique values.**threshold**[Optional]. The minimum percentage similarity between the colArray values that are not unique. Therefore a threshold value of 0.6 means that ULIST will look for partial matches and then eliminate any values with a 60 percent similarity and above. The default value is 1.

## HIGHLIGHT DUPLICATE VALUES

Flookup can be used to highlight duplicates in Google Sheets. You can either highlight duplicate values by percentage similarity or by sound similarity.

To highlight duplicate values by percentage similarity, use the menu item labelled "By percentage" [Flookup > Find duplicates > By percentage] and to highlight duplicate values by sound similarity, use the menu item labelled "By sound" [Flookup > Find duplicates > By sound].

### By Percentage

With this function, Flookup highlights duplicate values which have a specific minimum percentage similarity between them. The steps to highlight duplicates by this method are:

- Select a range.
- Find and click the menu item labelled "By percentage".
- A prompt is displayed requesting the user to enter the column index to analyse and the desired minimum percentage similarity. If no user input is made, values which have a 100% similarity match between them will be found and the first column the selected range will be analysed.

All selected values with the specified percentage similarity between them will be highlighted yellow.

### By Sound

With this function, Flookup highlights duplicate values based on sound similarity between the them. The steps to highlight duplicates by this method are:

- Select a range.
- Find and click the menu item labelled "By sound".
- A prompt is displayed requesting the user to enter the column index to analyse. If no user input is made, the first column the selected range will be analysed.

All selected values with similar sounds will be highlighted aqua.

## REMOVE DUPLICATE VALUES

Flookup can be used to remove duplicates in Google Sheets. You can either remove duplicate values by percentage similarity or by sound similarity.

To remove duplicates by percentage similarity, use the menu item labelled "By percentage" [Flookup > Delete duplicates > By percentage] and to remove duplicates by sound similarity, use the menu item labelled "By sound" [Flookup > Delete duplicates > By sound].

### By Percentage

With this function, Flookup finds and deletes selected rows that contain duplicate values with a specific minimum percentage similarity between them. The steps to delete duplicates by this method are:

- Select values in a range.
- Find and click the menu item labelled "By percentage".
- A prompt is displayed requesting the user to enter the column index to analyse and the desired minimum percentage similarity. If no user input is made, values which have a hundred percent similarity match between them will be found and the first column the selected range will be analysed.

All selected rows containing duplicate values, with the specified percentage similarity between them, will be deleted.

### By Sound

With this function, Flookup deletes selected rows that contain duplicate values based on the sound similarity between them. The steps to delete rows by this method are:

- Select a range.
- Find and click the menu item labelled "By sound".
- A prompt is displayed requesting the user to enter the column index to analyse. If no user input is made, the first column the selected range will be analysed.

All selected rows containing values with similar sounds will be deleted.

I hope you enjoyed this tutorial and found it helpful. You can find answers to any questions that might arise in the **official forum**. Please be sure to check it out.