Quick Learn Microsoft Excel - How To Use VLOOKUP() Function

Download The Spreadsheet Used In This Video

Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function syntax has the following arguments:

lookup_value  Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.

table_array  Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. 

col_index_num  Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

Note:

1. When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value.
2. When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value.
3. If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters — the question mark (?) and asterisk (*) — in lookup_value. 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.

Learn Microsoft Excel - How To Use FLOOR() Formula

Syntax
FLOOR(number,significance)
The FLOOR function syntax has the following arguments:
Number : Required. The value you want to round.
Significance : Required. The multiple to which you want to round.

Note:
If either argument is nonnumeric, FLOOR returns the #VALUE! error value. 
If number is positive and significance is negative, FLOOR returns the #NUM! error value. 
If the sign of number is positive, a value is rounded down and adjusted toward zero. If the sign of number is negative, a value is rounded down and adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Learn Microsoft Excel - How To Use CEILING() Formula

Syntax
CEILING(number,significance)
The CEILING function syntax has the following arguments:
Number : Required. The value you want to round.
Significance : Required. The multiple to which you want to round.

Note:
If either argument is nonnumeric, CEILING returns the #VALUE! error value. 
Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs. 
If number is negative, and significance is negative, the value is rounded down, away from zero.
If number is negative, and significance is positive, the value is rounded up towards zero.