For easy reference when doing a VLOOKUP function, add number on top of the data on client file/s.
You can now start mapping the data, copy the Employee ID from client data and paste it on Employees template/tab, then create (6.1) your VLOOKUP function on the first field you want to map, it can be either on Group (cell B3) if any or First Name (cell C3) field
Drag the VLOOKUP formula you've created (cell B3) horizontal or vertical or copy-paste it to the next cell. Do this until you finish mapping all the data
Note: Add an absolute ($) function, this way columns or rows will not be moved when you drag or copy-paste the formula.
Below are some Microsoft Excel functions that may be useful when you are mapping the data into your data template:
VLOOKUP = To map customer data to the data template.
https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
FORMULA: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
MEANING: =VLOOKUP(what you want to look up, where you want to look for it, the column number in the range containing the value to return, return to specify 1/TRUE for Approximate or 0/FALSE Exact Match)
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
value you want to look up also called the lookup value.
the range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
the column number in the range that contains the return value. For example, if you specify B2: D11 as the range, you should count B as the first column, C as the second, and so on.
Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be a TRUE or approximate match.
DOLLAR = converts a number to text using currency number format, with the decimals rounded to the number of places you specify.
https://support.office.com/en-us/article/dollar-function-a6cd05d9-9740-4ad3-a469-8109d18ff611
FORMULA: =DOLLAR(number, [decimals])
The DOLLAR function syntax has the following arguments:
number (Required) A number, a reference to a cell containing a number, or a formula that evaluates to a number.
decimals (Optional) The number of digits to the right of the decimal point. If this is negative, the number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2.
TEXT = converts a number to text by applying to format to it with format codes.
https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
FORMULA: =TEXT(value, format_text)
MEANING: =TEXT(Value you want to format, "Format code you want to apply")
The TEXT function syntax has the following arguments:
value A numeric value that you want to be converted to a text.
format_text A text string that defines the formatting that you want to be applied to the supplied value.
TRIM = Removes all spaces from text except for single spaces between words. Use TRIM on the text that you have received from another application that may have irregular spacing - to remove extra spacing and clean data.
https://support.office.com/en-us/article/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
FORMULA: =TRIM(text)
MEANING: =TRIM(the cell from where extra spaces will be removed)
The TRIM function syntax:
text The text from which you want spaces removed.
PROPER = Capitalizes the first letter in a text string and any other letters in the text that follow any character other than a letter. Converts all other letters to lowercase letters.
https://support.office.com/en-us/article/proper-function-52a5a283-e8b2-49be-8506-b2887b889f94
FORMULA: =PROPER(text)
MEANING: =PROPER(the cell that should be converted to the proper case)
The PROPER function syntax has the following arguments:
text (Required) Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.
IFERROR/IFNA = Trap and handle errors in a formula, returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
https://support.office.com/en-us/article/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
FORMULA: =IFERROR(value, value_if_error) or =IFNA(value, value_if_na)
The IFERROR or IFNA function syntax has the following arguments:
value (Required) The argument that is checked for an error.
value_if_error or value_if_na (Required) The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
ROUND = Rounding numbers 1-4 down or rounding numbers 5-9 up and rounds a number to a specified number of digits.
https://support.office.com/en-us/article/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
FORMULA: =ROUND(number, num_digits)
The ROUND function syntax has the following arguments:
number (Required) The number that you want to round.
num_digits The number of digits to which you want to round the number argument.
Use these functions as needed:
SUMIF/SUMIFs = Returns a conditional sum across a range or to sum the values in a range that meets the criteria that you specify.
https://support.office.com/en-us/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b
FORMULA: =SUMIF(range, criteria, [sum_range])
The SUMIF function syntax has the following arguments:
range The range of cells that you want to be evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).
criteria The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, "32", "apples", or TODAY().
-
IMPORTANT: Any text criteria or any criteria that include logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria are numeric, double quotation marks are not required.
sum_range The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria are applied).
You can use the wildcard characters—the question mark (?) and asterisk (*)—as the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
CONCATENATE = Allows you to combine text from different cells into one cell.
https://support.office.com/en-us/article/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d
FORMULA: =CONCATENATE(text1, [text2], ... ) or =CONCAT(text1, [text2], ... )
MEANING: =CONCATENATE(the first cell to join, 2nd cell to join, next cell to join...)
The CONCATENATE or CONCAT function syntax:
text argument
EXACT = Compares two text strings and returns TRUE if they are exactly the same, FALSE if otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
https://support.office.com/en-us/article/exact-function-d3087698-fc15-4a15-9631-12575cf29926
FORMULA: =EXACT(text1, text2)
MEANING: =EXACT(first cell to compare, 2nd cell to compare)
The EXACT function syntax has the following arguments:
text1 (Required) The first text string.
text2 (Required) The second text string.
