Excel Functions – Lookup & Reference

Excel Function	Description
Excel COLUMN Function	Excel COLUMN function can be used when you want to get the column number of a specified cell. Input Arguments [reference] – An optional argument that refers to a cell or range of cells. If this argument is omitted, COLUMN function returns the column number of the cell in which the formula resides. Additional Notes If the reference is a range of cells, COLUMN function returns the column number of the left-most column in the specified range. For example, =COLUMN(B2:D10) would return 2 as the left-most column is B2 for which the column number is 2. If the reference is entered as an array, COLUMN function returns the column numbers of all the columns in that array. Reference can not refer to multiple references or addresses. COLUMN function can be particularly helpful when you want to get a sequence of numbers in a row. For example, enter =COLUMN() in cell A1 and drag it to the right. You will have sequence of numbers 1.2.3..
Excel COLUMNS Function	Excel COLUMNS function can be used when you want to get the number of columns in a specified range or array. It returns a number that represents the total number of columns in the specified range or array. Input Arguments array – it could be an array, an array formula or a reference to a contiguous range of cells. Additional Notes Even if the array contains multiple rows and columns, only the columns are counted. For example: COLUMNS(A1:B1) returns 2. COLUMNS(A1:B100) also returns 2. This formula can be useful when you want to get a sequence of numbers as you go to the right in your worksheet. For example, if you want 1 in A1, 2 in B1, 3 in C1 and so on, use the following formula =COLUMNS($A$1:A1). As you would drag this to the right, the reference inside it would change and the number of columns in the reference would get incremented by one. For example, when you drag it to column B1, the formula becomes COLUMNS($A$1:B1) which then returns 2.
Excel HLOOKUP Function	Excel HLOOKUP function is best suited for situations when you are looking for a matching data point in a row, and when the matching data point is found, you go down that column and fetch a value from a cell which is specified a number of rows below the top row. Input Arguments lookup_value – this is the look-up value that you are looking for in the first row of the table. It could be a value, a cell reference, or a text string. table_array – this is the table in which you are looking for the value. This could be a reference to a range of cells or a named range. row_index – this is the row number from which you want to fetch the matching value. If row_index is 1, the function would return the lookup value (as it is in the 1st row). If row_index is 2, the function would return the value from the row just below the lookup value. [range_lookup] – (Optional) here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match (see additional notes below).
Excel INDEX Function	Excel INDEX function can be used when you have the position (row number and column number) of a value in a table, and you want to fetch that value. This is often use with the MATCH function and is a powerful alternative to the VLOOKUP function. Input Arguments array – a range of cells or an array constant. row_num – the row number from which the value is to be fetched. [col_num] – the column number from which the value is to be fetched. Although this is an optional argument, but if row_num is not provided, it needs to be given. [area_num] – (Optional) If array argument is made up of multiple ranges, this number would be used to select the reference from all the ranges.
Excel INDIRECT Function	Excel INDIRECT function can be used when you have the references as text and you want to get the values from those references. It returns the reference specified by the text string. Input Arguments ref_text – A text string that contains the reference to a cell or a named range. This must be a valid cell reference, or else the function would return a #REF! error [a1] – A logical value that specifies what type of reference to use for ref text. This could either be TRUE (indicating A1 style reference) or FALSE (indicating R1C1-style reference). If omitted, it is TRUE by default.
Excel MATCH Function	Excel MATCH function can be used when you want to get the relative position of a lookup value in a list or an array. It returns a number that represents the position of the lookup value in the array. Input Arguments lookup_value – The value for which you are looking for a match in the lookup_array. lookup_array – The range of cells in which you are searching the lookup_value. [match_type] – (Optional) This specifies how excel should look for a matching value. It can take three values -1, 0 , or 1 (read additional notes below for more info).
Excel OFFSET Function	Excel OFFSET function can be used when you want to get a reference which offsets specified number of rows and columns from the starting point. It returns the reference that OFFSET function points to. Input Arguments reference – The reference from which you want to offset. This could be a cell reference or a range of adjacent cells. rows – the number of rows to offset. If you use a positive number it offsets to the rows below, and if a negative number is used, then it offsets to the rows above. cols – the number of columns to offset. If you use a positive number it offsets to the columns to the right, and if a negative number is used, then it offsets to the columns on the left. [height] – this is a number that represents the number of rows in the returned reference. [width] – this is a number that represents the number of columns in the returned reference.
Excel ROW Function	Excel ROW Function function can be used when you want to get the row number of a cell reference. For example, =ROW(B4) would return 4, as it is in the fourth row. Input Arguments [reference] – An optional argument that refers to a cell or range of cells. If this argument is omitted, ROW function returns the row number of the cell in which the formula resides. Additional Notes If the reference is a range of cells, ROW function returns the row number of the top-most row in the specified range. For example, =ROW(B5:D10) would return 5 as the top-most row is B2 for which the row number is 5. If the reference is entered as an array, ROW function returns the row numbers of all the rows in that array. Reference can not refer to multiple references or addresses. ROW function can be particularly helpful when you want to get a sequence of numbers in a column. For example, enter =ROW() in cell A1 and drag it down. You will have sequence of numbers 1.2.3..
Excel ROWS Function	Excel ROWS Function can be used when you want to get the number of rows in a specified range or array. It returns a number that represents the total number of rows in the specified range or array. Input Arguments array – it could be an array, an array formula or a reference to a contiguous range of cells. Additional Notes Even if the array contains multiple rows and columns, only the rows are counted. For example: ROWS(A1:B3) would return 3. ROWS(A1:B100) would return 100. ROWS formula can be useful when you want to get a sequence of numbers as you go down the rows in your worksheet. For example, if you want 1 in A1, 2 in A2, 3 in A3 and so on, use the following formula =ROWS($A$1:A1). As you would drag this down, the reference inside it would change and the number of rows in the reference would get incremented by one. For example, when you drag it to row A2, the formula becomes ROWS($A$1:A2) which then returns 2.
Excel VLOOKUP Function	Excel VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right. Input Arguments lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name. table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2. [range_lookup] – here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match (see additional notes below).
Excel XLOOKUP Function	Excel XLOOKUP function is a new function for Office 365 users and is an enhanced version of the VLOOKUP/HLOOKUP functions. It can be used to lookup and fetch the value in a dataset, and can replace most of what we do with older lookup formulas. lookup_value – the value that you’re looking for lookup_array – the array in which you’re looking for the lookup value return_array – the array from which you want to fetch and return the value (corresponding to the position where the lookup value is found) [if_not_found] – the value to return in case the lookup value is not found. In case you don’t specify this argument, a #N/A error would be returned [match_mode] – Here you can specify the type of match you want: 0 – Exact match, where the lookup_value should exactly match the value in the lookup_array. This is the default option. -1 – Looks for the exact match, but if it’s found, returns the next smaller item/value 1 – Looks for the exact match, but if it’s found, returns the next larger item/value 2 – To do partial matching using wildcards (* or ~) [search_mode] – Here you specify how the XLOOKUP function should search the lookup_array 1 – This is the default option where the function starts looking for the lookup_value from the top (first item) to the bottom (last item) in the lookup_array -1 – Does the search from bottom to top. Useful when you want to find the last matching value in the lookup_array 2 – Performs a binary search where the data needs to be sorted in ascending order. If not sorted, this can give error or wrong results -2 – Performs a binary search where the data needs to be sorted in descending order. If not sorted, this can give error or wrong results
Excel FILTER Function	Excel FILTER function is a new function for Office 365 users that allows you to quickly filter and extract data based on the given condition (or multiple conditions). =FILTER(array,include,[if_empty]) array – this is the range of cells where you have the data and you want to filter some data from it include – this is the condition that tells the function what records to filter [if_empty] – this is an optional argument where you can specify what to return in case no results are found by the FILTER function. By default (when not specified), it returns the #CALC! error