A. THE USE OR USE
1. Left function
- Syntax: LEFT (text, number of characters to get) .
- Meaning: Returns the number of characters to start from the first character on the left.
- Example: LEFT ("TipsMake.vn", 8) = "thuthuat".
2. VLOOKUP function
- Syntax: VLOOKUP (lookup value, lookup table, return value column, lookup type) .
- For example:
Detector value: bar code column.
Table: TRA TABLE 1.
Return value: column name of company.
Detection type: Leave the value = 0 (accurate detection).
3. LEN function
- Syntax: LEN (text) .
- Meaning: Returns the number of characters in a string.
- Example: LEN ("TipsMake.vn") = 18.
4. MID function
- Syntax: MID (character string, starting position, number of characters to be taken) .
- Meaning: Returns the substring of a character in a given string starting at a certain position.
- Example: MID ("TipsMake.vn", 9, 9) = "phanmem.vn".
5. RIGHT function
- Syntax: RIGHT (text, number of characters to get) .
- Meaning: Returns the number of characters to start from the first character on the right.
- Example: RIGHT ("TipsMake.vn", 10) = "phanmem.vn".
6. The VALUE function
- Syntax: VALUE (text) .
- Meaning: Bringing string values as numbers into mathematical values.
- Example: Value ("0025") = 25.
7. HLOOKUP function
- Syntax: HLOOKUP (lookup value, lookup table, return value row, lookup type) .
B. GENERAL EXAMPLE
The following data sheet is available:
1. Find the names of the items based on TABLE 1
- Use the Vlookup and Left functions to get the name of the item corresponding to the item code in TABLE 1 .
Enter the formula: Vlookup (Left (B3, 1), $ A $ 13: $ B $ 15, 2, 0) .
2. Find the manufacturer based on the last 2 characters of the product's code
If the last 2 characters are "TN" -> "Domestic". If other characters are found in TABLE TRA 2 .
Enter the following statement: IF (RIGHT (B3, 2) = "TN", "Domestic", HLOOKUP (RIGHT (B3, 2), $ F $ 12: $ J $ 13, 2, 0)) .
3. Calculate the unit price based on the first letter of the code and TABLE 1
Results after calculation:
Good luck!