If you spend time on your Excel Project and you feel it can be done some other way, you may contact our Excel Help Team to help you with your Excel Project or use our Excel Question page.
More practical features can be found in our Excel Utility.
Excel's Lookup and Reference Functions can be used to simplify finding specific entries in a data table.
Download our FREE Excel Utility
Provide many features and shortcuts to your Excel use
VLookup function searches for value in the left-mo...
Address
In Excel, the Address function returns a text representation of a cell address. The syntax for the Address function is: Address( row, column, ref_type, ref_style, sheet_name ) row is the row number to use in the cell address. column is the column number to use in the cell address. ref_type is optional. It is the type of reference to use. It can be any of the following values: Value Explanation 1 Absolute referencing. For example: $A$1 2 Absolute row; relative column. For example: $A1 3 Relative row; absolute column. For example: A$1 4 Relative referencing. For example: A1 If this parameter is omitted, the Address function assumes that the ref_type is set to 1. ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values: Value Explanation TRUE A1 style referencing FALSE R1C1 style referencing If this parameter is omitted, the Address function assumes that the ref_style is set to TRUE. sheet_name is optional. It is the name of the sheet to use in the cell address. If this parameter is omitted, then no sheet name is used in the cell address. For example: Let's take a look at an example: =Address(4, 5) would return "$E$4" =Address(4, 5, 1) would return "$E$4" =Address(4, 5, 2) would return "E$4" =Address(4, 5, 3) would return "$E4" =Address(4, 5, 4) would return "E4" =Address(4, 5, 1, TRUE) would return "$E$4" =Address(4, 5, 1, FALSE) would return "R4C5" =Address(4, 5, 1, TRUE, "Sheet1") would return "Sheet1!$E$4
In Excel, the Areas function returns the number of ranges in a reference. The syntax for the Areas function is: Areas( reference ) reference is a range of cells. If you specify more than one range, you must surround your ranges with brackets. For example: Let's take a look at an example: =Areas(A1) would return 1 =Areas(A1:C3) would return 1 =Areas((A1:C3, B4:B5)) would return 2 =Areas((A1:C3, B4:B5, G8)) would return 3 =Areas(suppliers) would return 3, if we defined a named range called suppliers as: A1:C3, B4:B5, G8
In Excel, the Column function returns the column number of a cell reference. The syntax for the Column function is: Column( reference ) reference is optional. It is a reference to a cell or range of cells. Note: If the reference parameter is omitted, then the Column function assumes that the reference is the cell address in which the Column function has been entered in.
In Excel, the Columns function returns the number of columns in a cell reference. The syntax for the Columns function is: Columns( reference ) reference is a reference to a range of cells or an array. For example: Let's take a look at an example: =Columns(B4) would return 1 =Columns(A4:B6) would return 2 =Columns({1,2;3,4}) would return 2 =Columns({1,2,3,4;5,6,7,8}) would return 4 You can also defined a named range called suppliers that refers to Sheet1!$A$1:$C$8. Then using the Columns function, you could refer to this named range as follows: =Columns(suppliers) would return 3
The GetPivotData function returns data from a pivot table. It can retrieve summary data from a pivot table as long as the summary data is visible. The syntax for the GetPivotData function is: GetPivotData( pivot_table, name ) pivot_table is generally a named range that has been set up to point to the pivot table. name is the name of the summary value that you wish to retrieve. For Example: Let's take a look at an example. Below we have an Excel spreadsheet that has a pivot table on Sheet2. We've set up a named range called PivotTable that references this pivot table (Sheet2!$A:$E).
The GetPivotData function returns data from a pivot table. It can retrieve summary data from a pivot table as long as the summary data is visible. The syntax for the GetPivotData function is: GetPivotData( pivot_table, name ) pivot_table is generally a named range that has been set up to point to the pivot table. name is the name of the summary value that you wish to retrieve.
In Excel, the HLookup function searches for value in the top row of table_array and returns the value in the same column based on the index_number. The syntax for the HLookup function is: HLookup( value, table_array, index_number, not_exact_match ) value is the value to search for in the first row of the table_array. table_array is two or more rows of data that is sorted in ascending order. index_number is the row number in table_array from which the matching value must be returned. The first row is 1. not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter FALSE to find an approximate match, which means that if an exact match if not found, then the HLookup function will look for the next largest value that is less than value. Note: If index_number is less than 1, the HLookup function will return #VALUE!. If index_number is greater than the number of columns in table_array, the HLookup function will return #REF!. If you enter FALSE for the not_exact_match parameter and no exact match is found, then the HLookup function will return #N/A.
In Excel, the Hyperlink function creates a shortcut to a file or Internet address. The syntax for the Hyperlink function is: Hyperlink ( link, display_name ) link is a path to the file or the URL to the Internet address. display_name is optional. It is the value that appears in the cell. If this parameter is omitted, then the link will appear in the cell.
In Excel, the Index function returns either the value or the reference to a value from a table or range. Syntax #1 - Returning a value The first implementation of the Index function returns the value from a table or range. The syntax for the Index function is: Index( array, row_number, column_number ) range is a range of cells or table. row_number is the row number in the array to use to return the value. column_number is the column number in the array to use to return the value.
In Excel, the Indirect function returns the reference to a cell based on its string representation. The syntax for the Indirect function is: Indirect( string_reference, ref_style ) string_reference is a textual representation of a cell reference. ref_style is optional. It is either a TRUE or FALSE value. TRUE indicates that string_reference will be interpreted as an A1-style reference. FALSE indicates that string_reference will be interpreted as an R1C1-style reference. If this parameter is omitted, the Indirect function will interpret string_reference as an A1-style.
In Excel, the Lookup function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the Lookup function: Syntax #1 In Syntax #1, the Lookup function searches for value in the lookup_range and returns the value in the result_range that is in the same position. The syntax for the Lookup function is: Lookup( value, lookup_range, result_range ) value is the value to search for in the lookup_range. lookup_range is a single row or single column of data that is sorted in ascending order. The Lookup function searches for value in this range. result_range is a single row or single column of data that is the same size as the lookup_range. The Lookup function searches for the value in the lookup_range and returns the value from the same position in the result_range. Note: If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. If the value is smaller than all of the values in the lookup_range, then the Lookup function will return #N/A. If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value.
In Excel, the Match function searches for a value in an array and returns the relative position of that item. The syntax for the Match function is: Match( value, array, match_type ) value is the value to search for in the array. array is a range of cells that contains the value that you are searching for. match_type is optional. It the type of match that the function will perform. The possible values are: match_type Explanation 1 (default) The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. If the match_type parameter is omitted, the Match function assumes a match_type of 1. 0 The Match function will find the first value that is equal to value. The array can be sorted in any order. -1 The Match function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order. Note: The Match function does not distinguish between upper and lowercase when searching for a match. If the Match function does not find a match, it will return a #N/A error. If the match_type parameter is 0 and a text value, then you can use wildcards in the value parameter. Wild card Explanation * matches any sequence of characters ? matches any single character
In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell. The syntax for the Offset function is: Offset( range, rows, columns, height, width ) range is the starting range from which the offset will be applied. rows is the number of rows to apply as the offset to the range. This can be a positive or negative number. columns is the number of columns to apply as the offset to the range. This can be a positive or negative number. height is the number of rows that you want the returned range to be. width is the number of columns that you want the returned range to be.
In Excel, the Row function returns the row number of a cell reference. The syntax for the Row function is: Row( reference ) reference is optional. It is a reference to a cell or range of cells. Note: If the reference parameter is omitted, then the Row function assumes that the reference is the cell address in which the Row function has been entered in.
In Excel, the Rows function returns the number of rows in a cell reference. The syntax for the Rows function is: Rows ( reference ) reference is a reference to a range of cells or an array. For example: Let's take a look at an example: =Rows(B4) would return 1 =Rows(A4:B6) would return 3 =Rows({1,2;3,4}) would return 2 =Rows({1,2,3,4;5,6,7,8}) would return 2 You can also defined a named range called suppliers that refers to Sheet1!$A$1:$C$8. Then using the Rows function, you could refer to this named range as follows: =Rows(suppliers) would return 8
In Excel, the Switch function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE. The syntax for the Switch function is: Switch ( expression1, value1, expression2, value2, ... expression_n, value_n ) expression1, expression2, expression_n is a list of expressions that are evaluated. The Switch function is looking for the first expression that evaluates to TRUE. value1, value2, ... value_n is a list of values. The Switch function will return the value associated with the first expression that evaluates to TRUE. For example: Switch (SupplierID=1, "IBM", SupplierID=2, "HP", SupplierID=3, "Nvidia") In this example, if SupplierID is 1, then the Switch function will return "IBM". If SupplierID is 2, then the Switch function will return "HP". If SupplierID is 3, then the Switch function will return "Nvidia". VBA Code The Switch function can only be used in VBA code. For example: Dim LValue As String LValue = Switch (SupplierID=1, "IBM", SupplierID=2, "HP", SupplierID=3, "Nvidia")
In Excel, the Transpose function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter. The syntax for the Transpose function is: Transpose( range ) range is the range of cells that you want to transpose. Note: The range value in the Transpose function must be entered as an array. To enter an array, enter the value and then press Ctrl-Shift-Enter. This will place {} brackets around the formula, indicating that it is an array.
In Excel, the VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number. The syntax for the VLookup function is: VLookup( value, table_array, index_number, not_exact_match ) value is the value to search for in the first column of the table_array. table_array is two or more columns of data that is sorted in ascending order. index_number is the column number in table_array from which the matching value must be returned. The first column is 1. not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value. Note: If index_number is less than 1, the VLookup function will return #VALUE!. If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!. If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLookup function will return #N/A.
