Excel functions - INDEX/MATCH

MATCH

searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

LEFT

returns the first character or characters in a text string, based on the number of characters you specify; LEFT(text, [num_chars]); =LEFT(A2,4); First four characters in the first string; Sale

VLOOKUP function

(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match - indicated as 0/FALSE or 1/TRUE).

VLOOKUP Syntax

(lookup_value, table_array, col_index_num, [range_lookup]); =VLOOKUP(105,A2:C7,2,TRUE; =VLOOKUP("Fontana",B2:E7,2,FALSE) 1. lookup_value: The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in table-array. For example, if table-array spans cells B2:D7, then your lookup_value must be in column B. See the graphic below. Lookup_value can be a value or a reference to a cell 2. table_array: The range of cells in which the VLOOKUP will search for the lookup_value and the return value. The first column in the cell range must contain the lookup_value (for example, Last Name in the picture below.) The cell range also needs to include the return value (for example, First Name in the graphic below) you want to find. 3. col_index_num: The column number (starting with 1 for the left-most column of table-array) that contains the return value 4. range_lookup (optional): logical value that specifies whether you want VLOOKUP to find an approximate or an exact match: TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. FALSE searches for the exact value in the first column.

Match_type Behavior

1. 1 or omitted = MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE 2. 0 = MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order 3. -1 = MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on 4. MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}

INDEX Array form Syntax

INDEX(array, row_num, [column_num]) 1. array: Required. A range of cells or an array constant. If array contains only one row or column, the corresponding Row_num or Column_num argument is optional. If array has more than one row and more than one column, and only Row_num or Column_num is used, INDEX returns an array of the entire row or column in array. 2. Row_num: Required. Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required 3. Column_num: Optional. Selects the column in array from which to return a value. If Column_num is omitted, Row_num is required. NOTE: Row_num and Column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.

MATCH SYNTAX

MATCH(lookup_value, lookup_array, [match_type]) 1. lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value 2. lookup_array Required. The range of cells being searched 3. match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

INDEX Array form

Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant

INDEX

returns a value or the reference to a value from within a table or range. There are two ways to use the INDEX function: (1) If you want to return the value of a specified cell or array of cells, see Array form, (2) If you want to return a reference to specified cells, see Reference form.

LEN

returns the number of characters in a text string; LENB(text); =LEN(A2); Length of the first string; 11