1. ADDRESS Function
The ADDRESS Function returns a valid cell reference as per the column and row address. In simple words, you can create an address of a cell by using its row number and column number.
- row_num: A number to specify row number.
- column_num: A number to specify column number.
- [abs_num]: Reference type.
- [A1]: Reference style.
- [sheet_text]: A text value as a sheet name.
- By default, the ADDRESS function returns absolute reference in the result.
In the below example, we have used different arguments to get all types of results.
With R1C1 reference style:
- Relative reference.
- Relative row and absolute column reference.
- Absolute row and relative column reference.
- Absolute reference.
With A1 reference style:
- Relative reference.
- Relative row and absolute column reference.
- Absolute row and relative column reference.
- Absolute reference.
2. AREAS Function
The AREAS Function returns a number that represents the number of ranges in the reference you have specified. In simple words, it actually counts the different worksheet areas you have referred to the function.
- reference: A Reference to a cell or a range of cells.
- Reference can be a cell, a range of cells or a named range.
- If you want to refer to more than one cell reference, you have to enclose all those references in more than one set of parentheses and use commas to separate each reference from others.
In the below example, we have used areas function to get the number reference in a named range.
As you can see there are three columns in the range and it has returned 3 in the result.
3. CHOOSE Function
The CHOOSE function returns a value from the list of values based on the position number specified. In simple words, it looks for a value from a list based on its position and returns it in the result.
- index_num: A number for specifying the position of the value in the list.
- value1: A range of cells or an input value from which you can choose.
- [value2]: A range of cells or an input value from which you can choose.
- You can refer to a cell or you can also insert values directly in the function.
In the below example, we have used CHOOSE function with a drop-down list to calculate four(sum, average, max, and mix) different things. So, we have used the below formula to calculate all four things:
We have this small table with the name of all four calculations which we want and a serial number to each in the corresponding cell.
After that, we have a drop-down list for all four calculations. Now, to get index number in the choose function from that small table we have a lookup formula which will returns serial number as per the value selected from the drop-down list.
And instead of values, we have used four formulas for 4 different calculations.
4. COLUMN Function
The COLUMN function returns the column number for the given cell reference. As you know, every cell reference is made up of a column number and a row number. So it takes the column number and returns it in the result.
- reference: A cell reference for which you want to get the column number.
- You cannot refer to multiple references.
- If you refer to an array, the column function will also return the column numbers in an array.
- If you refer to a range of more than one cell, it will return the column number of the leftmost cell. For example, if you refer to the range A1:C10, it will return the column number of the cell A1.
- If you skip specifying a reference, it will return the column number of the current cell.
In the below example, we have used COLUMN to get the column number of the cell A1.
As I have already mentioned, if you skip specifying cell reference it will return the column number of the current cell. In the below example, we have used COLUMN to create a header with serial numbers.
5. COLUMNS Function
The COLUMNS function returns the number of columns referred to in the given reference. In simple words, it counts how many columns are there in the supplied range and returns that count.
- array: An array or range of cells from which you want to get the number of columns.
- You can also use a named range.
- COLUMNS function is not concerned with the values in the cells, it will simply return the number of columns in a reference.
In the below example, we have used COLUMN to get the number of columns from range A1:F1.
6. FORMULATEXT Function
The FORMULATEXT function returns the formula from the referred cell. And if there’s no formula in the referred cell, a value, or a blank, it will return a #N/A.
- reference: The cell reference from which you want formula as a text.
- If you refer to another workbook that workbook should be open, otherwise it will not show the formula.
- If you refer to a range more than a single cell, it will return formula from the upper-left cell of the given range.
- It will return an “#N/A” error value if the cell you are using as a reference does not contain any formula, has a formula with more than 8192 characters, a cell is protected, or an external workbook is not opened.
- If you refer two cells in circular reference it will return results from both.
In the below example, we have used formula text with different types of references. When you refer to a cell that doesn’t have any formula, it will return “#N/A” error value.
7. HLOOKUP Function
The HLOOKUP function lookups for a value in the top row of a table and returns the value from the same column of the matched value using the index number. In simple words, it performs a horizontal lookup.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value you want to lookup.
- table_array: The data table or an array from which you want to the lookup value.
- row_index_num: A numeric value representing a number of rows below from the top row from which you want the value. For example, if you specify 2 and your lookup value is in A10 in the data table, it will return value from cell B10.
- [range_lookup]: A logical value to specify the type of lookup. If you want to perform an exact match search use FALSE and if you want to perform a non-exact match use TRUE (Default).
- You can use wildcard characters.
- You can perform an exact match and an approximate match.
- While performing an approximate match make sure to sort data in ascending order from left to right, and if data is not in ascending order then it would return an inaccurate result.
- If range_lookup is true or omitted, it will perform a non-exact match but return an exact match if the lookup value exists in the lookup range.
- If range_lookup is true or omitted, and the lookup value is not in the lookup range, it will return the nearest value which is less than the lookup value.
- If range_lookup is false, then there is no need to sort data range.
In the below example, we have used the HLOOKUP function with MATCH to create a dynamic formula and then we have used a drop-down list to change the lookup value from the cell.
The zone name from cell C7 is used as a lookup value. Range B1: F5 as table array and for row_index_num we have used match function to get the row number.
Whenever you change the value in cell C9, it will return the row number from the table array. You don’t have to change your formula again and again. Just change values with the drop-down list and you will get value for that.
8. HYPERLINK Function
The HYPERLINK function returns a string with a hyperlink attached to it. In simple words, like the HYPERLINK option you have in Excel, the HYPERLINK function helps you to create a hyperlink.
- Link_Location: The location for which you want to add a HYPERLINK. It can be further split into two terms.
- link: It can be an address of a cell or range of cells in the same worksheet or in any other worksheet or in any other workbook. We can also link a bookmark from a word document.
- location: It can be a link to a hard drive, a server using the UNC path, or any URL from the internet or intranet. (In Excel online you can only use web address for HYPERLINK function). You can insert a link to the function by inserting it as a text with quotation marks or by referring to a cell containing the link as a text. Make sure to use “HTTPS://” before a web address.
- [friendly_name]: It is an optional part of this function. It acts as the face of the connecting link.
- You can use any type of text, number, or both.
- You also refer to a cell which contains the friendly_name.
- If you skip it, the function will use the link address to display.
- If friendly_name returns an error, the function will display error.
- Link a file saved on a Web Address: You can use a file that is saved on a web address. This helps us to share the file in an effective way.
- Link a file saved on a Hard Drive: You can also use this function while working offline. You can link a file that is stored on your hard drive and access them through your single excel sheet, no need to go to every single folder to open them.
- Link a Word Document File: This is also an awesome feature of HYPERLINK function. You can link a Word document file or a specific place in word document file using a bookmark.
- Link a file without using Friendly Name: If you want to show the actual link to the file or place to the user. In this situation, you just need to skip the friendly name declaration in the HYPERLINK Function.
9. INDEX Function
The INDEX function returns a value from a list of values based on its index number. In simple words, INDEX returns a value from a list of values and you need to specify that value’s position.
INDEX has two different syntaxes.
In the first, you can use an array form of an index to simply get a value from a list using its position.
INDEX(array, row_num, [column_num])
In the second, you can use a referral form that is less used in real life but you can use it if you have more than one range to get value from.
INDEX(reference, row_num, [column_num], [area_num])
- array: A range of cells or an array constant.
- reference: A range of cells or multiple ranges.
- row_number: The number of the row from which you want to get the value.
- [col_number]: The number of the column from which you want to get the value.
- [area_number]: If you are referring to more than one range of cells (using reference syntax), specify a number to refer to a range from all those.
- When both the row_num and column_num arguments are specified, it will return the value in the cell at the intersection of both.
- If you specify row_num or column_num as 0 (zero), it will return the array of values for the entire column or row, respectively.
- When row_num and column_num are out the range, it will return an error #REF!.
- If area_number is greater than the number ranges you have specified then it will return #REF!.
1. Using ARRAY – Getting Value from a List
In the below example, we have used the INDEX function to get the quantity of June month. In the list, Jun is in 6th position (6th row) that’s why I have specified 6 in row_number. INDEX has returned the value 1904 in the result.
And if you referring to a range with more than one column you have to specify the column number.
2. Using REFERENCE – Getting Value from Multiple Lists
In the below example, instead of selecting all the range in one go, I have selected it as three different ranges. In the last argument, we have specified 2 in area_number which will define the range to use from these three different ranges.
Now in the second range, we are referring to the 5th row and 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.
10. INDIRECT Function
The INDIRECT function returns a valid reference from a text string which represents a cell reference. In simple words, you can refer to a cell range by using the cell address as a text value.
- ref_text: A text which represents the address of a cell, an address of a range of cells, a named range, or a table name. For example, A1, B10:B20, or MyRange.
- [a1]: A number or a boolean value to represent the type of cell reference you are specifying in ref_text. For example, if you want to use A1 reference style use TRUE or 1 and if you want to use R1C1 reference style use FALSE or 0 for R1C reference style. And if you omit to specify the cell reference type, it will use A1 style as default.
- When you referred to another workbook, that workbook should be opened.
- If you insert a row or a column in the range which you have referred, INDIRECT will not update that reference.
- If you want to insert text directly into the function you have to put it in double quotation marks or you can also refer to a cell that has the text you want to use as a reference.
1. Reference to Another Worksheet
You can also refer to another worksheet using the INDIRECT and you have to insert the worksheet name in it. In the below example, we have used the indirect function to refer to another worksheet and have the sheet name in cell A2 and cell reference in cell B2.
In cell C2, we have used the following formula to combine the text.
This combination creates a text which is used by the INDIRECT function to refer to the cell A1 in sheet1 and the best part is when you change the worksheet name or cell address the reference will automatically change.
Cell A1 in “Sheet1” has the value “Yes” and that’s why indirect returns the value “Yes”.
2. Reference to Another Workbook
You can also refer to another workbook, in the same way, we did for another worksheet. All you have to do, just add a workbook name in your text which you are using as a reference.
In the above example, we have used the following formula to get the value from the cell A1 of the workbook “Book1”.
As we have the workbook name in cell “A2”, worksheet name in cell “B2” and cell name in cell “C2”. We have combined them to use as an input text in indirect function.
Note: While combining cell reference as a text make sure to follow the right reference structure.
3. Using with Named Ranges
Yes, you can also refer to a named range using the indirect function. It’s just simple. Once you create a named range you have to enter that named range as a text in INDIRECT.
In the above example, we have a drop-down in cell E1 which has a list of named ranges, and in cell E2 we have used that name. As range B2:B5 is named as “Quantity” and range C2:C5 is named as “Amount”.
When you select quantity from drop-down indirect function instantly refers to the named range. And when you select the amount from the drop-down, you will have the sum of cell range C2:C5.
11. LOOKUP Function
The LOOKUP function returns a value (which you are looking up) from a row, column, or from an array. In simple words, you can look up for a value, and LOOKUP will return that value if it’s there in that row, column, or array.
LOOKUP(value, lookup_range, [result_range])
There are two types of LOOKUP functions.
- Vector Form
- Array Form
- value: The value that you want to search from a column or a row.
- lookup_range: The column or row from which you want to lookup for the value.
- [result_range]: The column or row from which you want to return a value. This is an optional argument.
- Instead of using array form it’s better to use VLOOKUP or HLOOKUP.
12. MATCH Function
The MATCH function returns the index number of the value from an array. In simple words, the MATCH function looks up for a value in the list and returns the position number of that value in the list.
MATCH (lookup_value, lookup_array, [match_type])
- lookup_value: The value whose position you want to get from a list of values.
- lookup_array: The range of cell or an array contains values.
- [match_type]: The number (-1, 0 & 1) to specify how excel look for the value from the list of values.
- If you use 1, it will return the largest value which is equal or less than the lookup value. The values in the list must be sorted in ascending order.
- If you use -1, it will return the smallest value which is equal or greater than the lookup value. The values in the list must be sorted in ascending order.
- If you use 0, it will return the exact match from the list.
- You can use wildcard characters.
- If there is no matching value in the list if will return #N/A.
- The match function is non-case sensitive.
In the below example, we have used 1 as match type and we are looking for value 5.
As I have already mentioned if you use 1 in match type it returns the largest value which is equal or smaller than the lookup value. In the entire list, there are 3 values that are smaller than 5 and 4 is the highest in them.
That’s why in the result it has returned 3 which is the position of value 4.
13. OFFSET Function
The OFFSET function returns a reference to a range which is a specific number of rows and columns away from a cell or range of cells. In simple words, you can refer to a cell or a range of cells by using rows and columns from a starting cell.
OFFSET(reference, rows, cols, [height], [width])
- reference: The reference from which you want to offset to start. It can be a cell or range of adjacent cells.
- rows: The number of rows that tell OFFSET to move up or down from the reference. To go downward you need a positive number and for going upwards you need a negative number.
- cols: The number of columns tells OFFSET to move to the left or right from the reference. To go right you need a positive number and for going left you need a negative number.
- [height]: A number to specify the rows to include in the reference.
- [width]: A number to specify the columns to include in the reference.
- OFFSET is a “volatile” function, it recalculates whenever there is any change to a worksheet.
- It displays the #REF! error value if the offset is outside the edge of the worksheet.
- If height or width is omitted, the height and width of reference are used.
In the below example, we have used SUM with OFFSET to create a dynamic range which sums the values from all the months for a particular product.
14. ROW Function
The ROW function returns the row number of the referred cell. In simple words, with the ROW function, you can get the row number of a cell and if you don’t refer to any cell then it returns the row number for the cell where you insert it.
- reference: A cell reference or a range of cells for which you want to check the row number.
- It will include all types of sheets (Chart Sheet, Worksheet or Macro Sheet).
- You can refer to sheets even if they are visible, hidden or very hidden.
- If you skip specifying any value in the function it will give you the sheet number of the sheet in which you have applied the function.
- If you specify an invalid sheet name, it will return a #N/A.
- If you specify an invalid sheet reference, it will return a #REF!.
In the below example, we have used the row function check the row number of the same cell where we have used the function.
In the below example, we have referred to another cell to get the row number of that cell.
You can use the row function to create a serial number list in your worksheet. All you have to do is just enter row functions in a cell and drag it up to the cell you want to add serial numbers.
15. ROWS Function
The ROWS function returns a count of the rows from the referred range. In simple words, with the ROWS function you can count how many rows are in the range you have referred to.
- array: A cell reference or an array to check the number of rows.
- You can also use a named range.
- It is not concerned with the values in the cells, it will simply return the number of rows in a reference.
In the below example, we have referred to a vertical range of 10 cells and it has returned 10 in the result as the range includes 10 rows.
16. TRANSPOSE Function
The TRANSPOSE function changes the orientation of a range. In simple words, by using this function you can change the data from a row into a column and from a column into a row.
- array: An array or a range you want to transpose.
- You have to apply TRANSPOSE as an array function, using the same number of cells as you have in your source range by pressing Ctrl + Shift + Enter.
- If you select cells less than source range, it will transpose data only for those cells.
Here we need to transpose data from range B2:D4 to range G2 to I4:
For this, first, we need to go to the cell G2 and select cell range up to I4.
Next is to enter (=TRANSPOSE(B2:D4)) in cell G2 and press Ctrl+Shift+Enter.
TRANSPOSE will convert the data from the rows into columns, and the formula which we have applied is an array formula, you cannot change a single cell from it.
17. VLOOKUP Function
The VLOOKUP function lookups for a value in the first column of a table and returns the value from the same row of the matched value using the index number. In simple words, it performs a vertical lookup.
- lookup_value: A value that you want to search in a column. You can refer to a cell that has the lookup value or you can directly enter that value into the function.
- table_array: A range of cells, a named range from which you want to look up the value.
- col_index_num: A number represents the column number from which you want to retrieve the value.
- range_lookup: Use false or 0 to make an exact match and true or 1 for an appropriate match. The default is True.
- If VLOOKUP cannot find the value you are looking for, it will return an #N/A.
- VLOOKUP is only able to give you the value which is on the right side of the lookup value. If you want to look upon the right side, you can use INDEX and MATCH for that.
- If you are using an exact match then it will only match the value which is first in the column.
- You can also use wildcard characters with VLOOKUP.
- You can use TRUE or 1 if you want an appropriate match and FALSE or 0 for an exact match.
- If you are using an appropriate match (True): It will return the next smallest value from the list if there is no exact match.
- If the value which you are looking for is smaller than the smallest value in the list, VLOOKUP will return #N/A.
- If there is an exact value exists which you are looking for, it will give you that exact value.
- Make sure you have sorted the list in ascending order.
1. Using VLOOKUP for Categories
In the below example, we have a list of students with marks they have scored, and in the remarks column, we want to a grade according to their marks.
In the above marks list, we want to add remarks as per the below category range.
In this, we have two options to use.
FIRST is to create a nesting formula with IF which is a little bit time-consuming, and the SECOND option is to create a formula with VLOOKUP with an appropriate match. And, the formula will be:
How it works
I am using the “MIN MARKS” column to match the lookup value and I am getting value in return from the “Remarks” column.
I have already mentioned that when you use TRUE and there is no exact match lookup value then it will return the next smallest value from the lookup value. For example, when we are looking for a value 77 from the category table, 65 is the next smallest value after 77.
That is why we got “Good” in remarks.
2. Handling Errors in VLOOKUP Function
One of the most common problems which come when you are using VLOOKUP is that you’ll get #N/A whenever there is no match is found by it. But the solution to this problem is simple and easy. Let me show with an easy example.
In the below example, we have a list of names and their age and in cell E6, we are using the VLOOKUP function to look up a name from the list. Whenever I type a name that is not on the list I am getting #N/A.
But what I want here is to show a meaningful message instead of the error. The formula will be: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
How it works: IFNA can test a value for #N/A and if there is an error you can specify a value instead of the error.