Google Spreadsheet 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 Spreadsheet.

##### 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 each 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(A2, "Jack", "Mike",1)`

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.

Antonio Luppetti

http://woorkup.com/2010/02/19/10-useful-google-spreadsheet-formulas-you-must-know/