Google Sheets is a powerful and free tool to manage complex spreadsheets. This tutorial illustrates ten useful formulas you must know to help you simplify the way you work in Google Sheets.

### Vertical Look Up

This formula searches for value in the left-most column of array and returns the value in the same row based on the index.

=vlookup(search_criterion, array, index, sort_order)

For example, if you want to find the population of the value contained in the cell C2, from all values contained in the range A2:B4, copy and paste the following formula into the cell D2:

=vlookup(C2;A2:B4;2;false)

### Filter

This formula returns a filtered version of the given source array.

=filter(range; condition1, condition2,...)

In this example I filtered all values contained in the column A (range**A2:A12**) greater than **37**.

Copy and paste the following formula into the cell B2:

=filter(A2:A12;A2:A12>37)

You can also use multiple conditions to filter a source array:

=filter(A2:A12;A2:A12>37; A2:A12<60)

In this case, the formula returns all values contained in the range A2:A12 greater than 37 AND less than 60.

### Concatenate

This function concatenates several text strings, contained in different cells, into one string.

=concatenate(text1, text2, text3,…)

If you want to concatenate all values contained in the cells A2, A3, A4 into one string, copy and paste this formula into the cell B2:

=concatenate(A2,A3,A4)

If you want to separate every single value with a space (cell B3), use this formula:

=concatenate(A2," ", A3," ", A4)

### Find

This formula looks for a string of text within another string and returns an integer.

=find(find_text, text, position)

For example, if you want to find the “space” within the string contained in the cell C2 (which contains this text: Jack Bauer), copy and paste the following formula into the cell B2:

=find(" ", A2, 1)

This formula returns “5” that means the “space” is found within the source string after 4 characters.

### Left and Right

**Left** extracts a substring from a string, starting from the left-most character.

=left(text, number)

**Right** extracts a substring from a string, starting from the right-most character.

=right(text, number)

If you want to extract the name “Jack” from the cell A2, copy and paste the following formula into the cell B2:

=left(A2;4)

If you want to extract the surname “Bauer” from the cell A3, copy and paste the following formula into the cell B3:

=right(A3;5)

You can also use the following formula to extract dynamically the name from an array of multiple values (Jack Bauer, Gregory House, Christian Troy, …):

=left(A2; find(" ", A2, 1)-1)

### Split

This formula splits text based on the given delimiter, putting each section into a separate column in the row.

=split(string, delimiter)

For example, if you want to split the values contained in the column A, using the delimiter ” ” (space), copy the following formula into the column B (the column C will be populated automatically):

=split(A2," ")

### Substitute

This formula substitutes new text for old text in a string.

=substitute(text, search_text, new text, occurrence)

If you want to substitute “Jack” with “Mike” from the text contained in the cell A2, copy and paste this formula into the cell B2:

=substitute(text, search_text, new text, occurrence)

Occurrence (optional, in this case “1”) indicates how many occurrences of the search text are to be replaced.

### Unique

This formula returns only the unique values in the source array, discarding duplicates.

=unique(array)

If you want to obtain an array of unique values from the values contained in the column A, copy and paste the following formula into the cell B2:

=unique(A2;A9)

As you can see, this formula returns only unique values in the column A, discarding all duplicates.

### Google Finance

This function returns market information from Google Finance related to a specific company.

=GoogleFinance(symbol, attribute)

For example, if you want to know the Google stock price, copy and paste the following formula into the cell B2:

=GoogleFinance(A2,"price")

You can use the same formula for the other companies in this example (NVidia, Intel, Cisco,…). Other attributes are volume, earning per share, opening price, and so on (take a look at the official documentation for a full list).

### GoogleLookup

This formula attempts to find the values for straightforward facts about specific things.

=GoogleLookup(entity, attribute)

For example if you want to find the countries of cities listed in the column A, copy and paste the following formula into the column B:

=googleLookup(A2;"country")

You can access some types of entities such as countries and territories (population, largest city), rivers (origin, length), actors and musicians (date of birth, nationality), planets, and so on. For a full list of popular entities, take a look at the official documentation.