Spreadsheet

see: Google Docs-Editors Help

Attention, the formulas in the documentation are sometimes documented with , instead of using ;

Useful column formulas

Add text columns (returns: Invitation for: ... and ...)

Add texts

=CONCATENATE("Einladung für: ";Kontakt!A1;" und ";Kontakt!A2)

Text columns and "Add (results in special offer ...")

=CONCATENATE("Sonderangebot " & CHAR(34);D154; CHAR(34) )

Replace values (all comma replaced by semicolon)

=SUBSTITUTE( Kontakte!A1 ;",";";")

Letters "lowercase" (from "A" becomes "a")

=LOWER(Kontakte!A1)

Conditionally set value ("D" becomes "Deutschland")

=IF( Kontakte!A1="D";"Deutschland";Kontakte!A1)

Join and trim text cells: ("D " becomes "D")

=TRIM(Kontakte!A1 & " " & Kontakte!A2 & " "&Kontakte!A3)

One of the values then corresponds (from A, B or D "abc" otherwise becomes "...")

=IF(OR(Kontakte!A1="A" ; Kontakte!A1="B";Kontakte!A1;"C"); ”abc”; ”...”)

Conditionally replace areas (from 05202 to +495202)

=SUBSTITUTE(IF( Kontakte!A1 = "D"; IF(LEN(Kontakte!A2)>0;"+49 " & RIGHT(Kontakte!A2;LEN(Kontakte!L2)-1);"");Kontakte!A2);",";";")

Remove TABS

=SUBSTITUTE(CONCATENATE(A2);CHAR(9);";")

LINE BREAK (replace hard line break with soft line break)

=SUBSTITUTE(A2;CHAR(10);CHAR(13))

Space from left to a space (from 1.1.2000 13:12:04 becomes 1.1.2000)

=if (A2="";"";LEFT(A2;FIND(" ";A2;1)))

line counter

=ROW(A1)See if in column B text entries e.g. Companies are duplicated (does not differ from uppercase to lowercase)

=COUNTIF($B$2:$B$4182;B2)>1

See if column "C" has the text in column "A" in the customer list worksheet. (Small capitalization considered)

=IF(SUMPRODUCT(EXACT(Kundenliste!$A$2:$A$1000;C3))=1;"ok";"KUNDE?")

=SUBSTITUTE(A2;CHAR(10);CHAR(13))

First name and name from a text

first given name

=LEFT(EXPORT!AL2;LEN(EXPORT!AL2)-LEN(TRIM( RIGHT( SUBSTITUTE( EXPORT!AL2 ; " " ; REPT( " " ; 100 ) ) ; 100 ) )))

Surname

=TRIM( RIGHT( SUBSTITUTE( EXPORT!AL2 ; " " ; REPT( " " ; 100 ) ) ; 100 ) )

Read key values from a list

LOOKUP "ALL VALUES MUST BE AVAILABLE, otherwise the next closer value will be taken"

VLOOKUP "FALSE -> does not show the closer value and is more accurate when it comes to finding a key value."

You want to replace a value from a table with a key.

LOOKUP(search_key, search_range|search_result_array, [result_range])

Looks through a sorted row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column.

Syntax

search_key - The value to search for in the row or column. For example, 42, "Cats", or I24.
search_range|search_result_array - One method of using LOOKUP is to provide a single row or column search_range to look through for the search with a second argument result_range. The other way is to combine these two arguments into one search_result_array where the first row or column is searched and a value is returned from the last row or column in the array.
result_range - [ OPTIONAL ] - The range from which to return a result. The value returned corresponds to the location where search_key is found in search_range. This range must be only a single row or column and should not be used if using the search_result_array method.

Notes

The LOOKUP function will only work properly if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other related functions if data is not sorted.

If search_key is not found, the item used in the lookup will be the value that’s immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key is 2, then 1 will be used for the lookup.

In the search_result_array method, the last row or column in the provided range is returned. If a different row or column is desired, use VLOOKUP or HLOOKUP instead.

When using the search_result_array method, if the range provided contains more columns than rows, then the search will be from left to right over the first row in the range. If the range contains an equal number of rows and columns or more rows than columns, then the search will be from top to bottom over the first column in the range.

practical example

From the contact export we have a table with the fields of the customer number. The column Company still has to be filled with the company name. For this, the formula for the company in the 1st tab "Order" per row is filled with a LOOKUP on 2nd tab "Company List".

Lookup will find the nearest value (1002 missing in the company list). The return value is then 1001 Daimler

=LOOKUP(VALUE(B2);'Company List'!$A$2:$A$22;'Company List'!$B$2:$B$22)

With VLookup exact value is found (in the company list 1002 is missing. The return value is then # N / A.

=VLOOKUP(VALUE(D2);'Company List'!$A$2:$A$4;1;FALSE)

Formelbespiel Lookup

VLOOKUP "exact search"

As with the LOOKUP, VLOOKUP is also looking for an area. The result returns an error if the key-value is not found.

VLOOKUP(Suchschlüssel; Suchbereich; index; FALSE)

index is the relative value to be returned from the horizontal list.

The FALSE stands for exact key If you use TRUE, the next possible one is found as with the LOOKUP.

Relative references and absolute references (Google Sheets)

In our example, the formula would initially look like this:

=LOOKUP(VALUE(B2);'Company List'!A2:A22;'Company List'!B2:B22)

If this formula is now copied to further lines, the columns and lines are automatically adjusted and result in:

=LOOKUP(VALUE(B3);'Company List'!A3:A23;'Company List'!B3:B23)

Thus, the search area for the LOOKUP is no longer used correctly.

RIGHT it is to determine the values over $ for the copy: