Top 100 Excel Functions with Examples – A Complete List Including Samples Files

I’m sure you agree with me that "Functions can make you great in Excel".

Well, once you master Excel basics this is the next thing you need to master.

But the question is:

In the end, you have links to the relevant sources to learn further. And all the functions are structured in the same way.

What’s an Excel Function (Worksheet)?

In Excel, a function is a predefined formula which performs a specific calculation by using values which a user input as arguments.

Every Excel function has a specific purpose, in simple words, it calculates a specific value.

Each function has its own arguments which you need to specify to get the result value in the cell.

Components of an Excel Function

To understand an Excel function you need to understand its components:

  • Function Name
  • Parentheses
  • Arguments

Every Excel function has its name, which actually helps you to identify its purpose...

...then you need to enter opening parentheses after that specify it’s arguments, and in the end...

...enter closing parentheses.

Every Excel function has its name, which actually helps you to identify its purpose, then you need to enter opening parentheses after that specify it’s arguments, and in the end, enter closing parentheses.

Type of Arguments

There are two different types of arguments:

  • Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
  • Optional: If you skip specifying these arguments it will not stop a function to calculate its result value.

In this functions guide, I’ll be using the square bracket for every optional argument to make you understand.

Quick Note: There are some functions in Excel which don’t require any arguments, in that case, all you need to do is create type opening and closing parentheses and hit enter.

How to Insert a Function in Excel

The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with a equals to sign.

So if you want to insert the SUM function:

  • First of all, you need to type = and the then type SUM.
  • After that, enter the opening parentheses.
  • Specify the arguments (refer to a cell or you can directly enter values into the function).
  • In the end, type closing parentheses and hit enter.

Sample Files

A Complete List of Top 100 Excel Functions with Examples which YOU should Learn this Year to 10X YOUR SKILLS in No Time

Text Functions

If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.

1. LEFT Function

Examples

In the below example, we have extracted the first five digits from the text string using LEFT by specifying the number of characters to extract.

extract excel champs using left function

In the below example, we have used LEN and FIND along with the LEFT to create a formula which extracts the name from the cell.

extract first name using left function in excel

It first counts total length of the text string and then finds the position of space between first and last name.

And in the end, it extracts the first name from the test string.

Notes
  1. num_char can be zero or greater than zero.
  2. If you skip specifying num_char, it will assume 1 as default.
  3. You can’t refer to more than one cell. If you do that it will return the characters from the top right cell of the selected range.
  4. It can work with numbers also in the same manner.
  5. It can also work as an array formula.
  6. With negative num_char, it will return an error.
  7. You can also enter a text string directly into the formula.

2. RIGHT Function

extract excel champs using left function
get last name using right function
how right function get last name from cell

3. MID Function

simple examples to understand usages mid function in excel

4. LOWER Function

example for excel lower function

5. UPPER Function

Compare Excel UPPER-Function With Other Cases
Use Excel UPPER Function To Convert Lower Case Name Into Upper Case

6. PROPER Function

compare text return by proper function with other cases
convert all type of text into proper case using function

7. REPT Function

In the below example, we have used different types of text for repetition using with REPT function.

how to use excel rept function with different signs

It can repeat any type of text or numbers and even symbols which you specify in function and the main use of REPT function is for creating in-cell charts.

In the below example, we have used REPT function to create an in-cell column chart. You can use REPT Function in different ways to create some good looking tiny charts.

8. FIND Function

using mid len functions with find to get name

9. SEARCH Function

get position of pivot from the text using search function

10. LEN Function

count length of hello world with len function
example of count length of a date using len function

Date Functions

Dates are one of the major ingredients of data which you use every day, and if you want to analyze your data in a better way you need to know some of the Excel’s date functions.

11. DATE  Function

Examples

In the below example, I have used cell references to specify the year, month and day to create a date.

create a valid date with excel date function

You can also insert arguments directly into the function to create a date as you can see in the below example.

input arguments directly into excel date function
input arguments directly into excel date function

And in the below example, I have used different types of arguments to see the result returned by the function.

input different arguments in excel date function
  • 1900 as the year for the date.
  • If we mention -1 as a month it has gives us the date of November month of the last year. The point is, when you specify a year, month or day in negative number it will deduct that number from the starting month of the year you have mentioned.
  • And, if you mention 0 as a month number it will give you the date of last month of the previous year.
  • If you specify zero as a year number it will give you 1900 in the year as excel use dates from 1900 to 10000.
  • When you mention zero as a day it will return last day of the previous month.
  • And, if you mention the day in negative number it will deduct those days from last day of the previous month.
Notes
  1. You can enter year from 1900 to 10000. If you enter any year below than 1900 it will that number to 1900. For example, if you enter the year as 102 it will return the year 2002.
  2. It is recommended to enter a four-digit number for the year to avoid any issue.
  3. Year, Month and Day can be negative or positive.
  4. Excel store dates as serial numbers.

12. DATEDIF Function

13. DATEVALUE Function

Examples

In the below example, I have inserted a date directly into the function by using double quotation marks. If you skip adding these quotation marks it will return a #NAME? error in the result.

excel datevalue function to get a date serial number

In the below example, all the dates on the left side are in textual format.

excel datevalue function to get a date serial number
  • A simple textual date which I have converted into a valid date.
  • A date with all three components (Year, Month, or Day) in numbers.
  • If there is no year in your textual date, it will take the current year as the year.
  • And, if you have month name is in alphabets and no year.
  • If you don’t have the day in your textual date it will take 1 as the day number.
Notes
  1. You can directly enter a date into the function by using double quotation mark.
  2. When you insert a textual date in the function it will simply give you a serial number. You can convert that serial number into a date by changing the format to date or by using the shortcut key : Control + Shift + #.
  3. If you use a date which has no month in it, it will not convert that date into a date serial number.

14. EDATE Function

Examples

Below I have used EDATE with different types of arguments.

using edate function with different arguments
  1. In the first example, I have used 5 as numbers of months and it has added exactly 5 months in 1-Jan-2016 and returned 01-June-2016.
  2. In the second example, I have used -1 months and it has given 31-Dec-2016, a date which is exactly 1 month back from 31-jan-2016.
  3. In the third example, I have inserted a date directly into the function.
Notes
  • Use a positive number in “months” to get a future date and a negative number for a past date.

15. DAY Function

Examples

In the below example, I have used DAY function to simply get day from a date.

extract day number from a date using excel day function

And in the below example, we have used DAY with TODAY to create a dynamic formula which returns current day number and it will update every time you open your worksheet or when you recalculate your worksheet.

get dynamic day number by using excel day function
Notes
  1. It will only return day number from a date with year ranging from 1900 to 10000.
  2. You can also insert a date directly into the function by using quotation marks.

16. DAYS Function

Examples

In the below example, I have referred the cell A1 as the start date and B1 as the end date and I have 9 days in the result.

You can also use subtract operator to get the difference between two dates.

Enter Days In Excel Days Function

In the below example, I have directly inserted two dates into the function to get the difference between them.

Days function works with other date and time functions to create some dynamic formulas.

Enter Date Directly In Excel Days Function
Notes
  • Both start date and end date should be a valid date.
  • And, both should be within the range of valid dates. Excel accept dates which are from 1900 to 10000 year.
  • You can also enter a date directly into the function by using double quotation marks.

17. TODAY Function

Examples

In the below example, I have used today function with other functions to get current month number, current year and current day.

Use Excel TODAY Function With Other Function

You can use this function with other function to create some dynamic formulas.

The best example is when you need the current date in your formula, you can use today and you don’t have to enter date every time.

Notes
  1. It is a volatile function which will recalculate every time a calculation in performed.
  2. Today function returns a valid date serial number as per excel’s date system & you can use this date further with other calculations.
  3. When you enter today function in a cell where you have “General” number format, excel will convert that format into date format once you enter your formula.
  4. To enter a static date in a cell you can use the shortcut key Control + ;.

18. MONTH Function

Examples

In the below example, we have referred the cell A1 as the start date and B1 as the end date and we have 9 days in the result.

It will always give the number of current month whenever date changes.

Excel-Month-Function-With-Today-Function

In the below example, we have used month function along with CHOOSE and TODAY.

This dynamic formula will return the name of the month instead of month number.

Excel-Month-Function-With-Today-Function
Notes
  1. Date from which you want to get the month number should be a valid date.
  2. If you mention an invalid date it will return #VALUE! error.
  3. If you skip entering any value in serial_number it will return 0.

19. EOMONTH Function

Examples

Here in the below examples I have used this EOMONTH with different types of arguments.

Here in the below examples I have used this EOMONTH with different types of arguments.

examples about how to use eomonth function in excel
  • I have mentioned 01-Jan-2016 as the start date and 5 months for getting a future date. As June is exactly 5 months after January, it has returned 30-Jun-2016 in the result.
  • As I have already mentioned, EOMMONTH is smart enough to evaluate the total number of days in a month.
  • If you mention a negative number, it simply returns a past date which is the number of months back you have mentioned.
  • In the fourth example, I have used a date which is in text format and it has returned the date without returning any error.
Notes
  • If the start date which you have provided is not a valid date it returns #NUM!.
  • After adding or subtracting months from the start date, if that date is invalid EOMONTH Function will return #NUM!.
  • This function is smart enough to check the total number days while giving last day of a month. It doesn’t matter if there is 30 days, 31 days or 29 days in a month.

20. YEAR Function

Examples

In the below example, I have used year function to get year number from the dates. You can use this function where you have dates in your data and you only need year number.

use excel year function with to get year number

In the below example, I have used today function to get year number from the current date.

It will always update the year whenever you recalculate your worksheet.

Notes
  1. Excel store dates as serial numbers.
  2. It will only return year number if reference date is valid.
  3. You can only extract year number from a date which is after 01-01-1900.
  4. It always returns year in four digits.

21. WEEKDAY Function

Examples

In the below example, I have used weekday function with TODAY to get dynamic weekday.

It will give you the weekday whenever current date changes.

You can use this method in your dashboards to trigger some values when needs to change when weekday change.

Examples For Excel WEEKDAY Function

In the below example, I have used weekday with IF to create a formula which first checks weekday of a date and return “Weekday” or “Weekend” basis on the value return from weekday function.

Examples For Excel WEEKDAY Function With IF Function
Notes
  • You can also use a date which is in textual form, a result of another function, or you can enter a date directly into the function by using double quotation marks.
  • If you skip specifying return type it will take 1 as default.

22. WEEKNUM Function

Examples

In the below example, I have used TODAY with weeknum to get the week number of the current date.

It will update the week number automatically every time when the date is changed.

Examples To Understand Excel Weeknum Function With Today Function

In the below example, I have added text “Week-” with the week number for a meaningful result.

Examples To Understand Excel Weeknum Function
Notes
  • Excel stores the dates in serial numbers so that you can only use date which has the valid serial numbers.
  • If the serial number of date is out of range for the current date base value, a #NUM! is returned.
  • If the return type is out of the range specified in the table above, a #NUM! is returned.

23. NETWORKDAYS Function

Examples

In the below example, I have specified 10-jan-2015 as a start date and 20-Feb-2015 as an end date.

We have total 41 days between these two dates, out of which 11 days are weekends.

After deducting those 11 days it has returned 30 working days and I haven’t mentioned any holiday in this example.

Example For Excel NETWORKDAYS Function Without Holidays

Now, in below example with the same start and end dates, I have specified a holiday and, after deducting 11 days of weekend and 1 holiday it has returned 29 working days.

Example For Excel NETWORKDAYS Function With Holidays

Again with the same start and end dates, I have used a range of three cells for holidays to deduct from the calculation and, after deducting 11 weekend days and 3 holidays which I have mentioned It has returned 27 working days.

Example For Excel NETWORKDAYS Function With More Than One Holiday
Example For Excel NETWORKDAYS Function With More Than One Holiday
Notes
  • This function is a part of Analysis Tool Pack. If you find that it is not working properly you can reload your Analysis Tool Pack.
  • It will not include Saturday and Sunday by default.
  • You can enter a date using following methods.
  • Referring to a cell which contains the date value.
  • Inserting a date directly into the function by using double quotation marks.
  • Inserting a serial number which represents a date as per excel’s date system.
  • If any of the argument you have specified is not a valid date, it will return #VALUE!.
  • If you specify a holiday which is a weekend day as well, that will be concluded only once.

24. NETWORKDAYS.INTL Function

Examples

Here I have some examples which will help you to understand this function in a better way.

Example-1

In below example, I have used 01-Jan-2015 as a start date and 20-Jan-2015 as an end date.

I have specified 1 to take Sunday – Saturday as a weekend.

Example For Excel NETWORKDAYS.INTL Function Without Holidays

The function has returned 14 days after excluding 6 weekend days.

Example-2

Below, I have used same dates. And, I have used 11 in for weekend days which means it will only consider Sunday as a weekend.

Along with that, I have also used 10-Jan-2015 as a holiday.

Example For Excel NETWORKDAYS.INTL Function With Holidays

Now we have total 3 Sundays between both dates and a holiday. After excluding all these days the function has returned 16 days in the result.

Example – 3

In below example, I have used range to specify holidays. If you have more than one date for holidays you can refer to an entire range.

Example For Excel NETWORKDAYS.INTL Function With Multiple Holidays

Quick Tip: If you want to create a dynamic range for holidays, you can use a table for that.

Example – 4

If you want to choose custom days to count as working days ora weekend, you can use below format in the weekend argument.

Example For Excel NETWORKDAYS.INTL Function With Non Consective Days

Here, 0 represents a working day and 1 represents a non-working day. And, total seven numbers represent 7 days of a week.

Example For Excel NETWORKDAYS Function With More Than One Holiday

You can use 0 for the days want to take as a working day and 1 for weekends. Here I have specified Monday and Thursday as working days.

Notes
  • If a start date is greater than ending date, it will return days in negative.
  • If start date or end date is a date which is out of the range, it will return a #NUM!.
  • You can enter a date using following methods.
  • Referring to a cell which contains the date value.
  • Inserting a date directly into the function by using double quotation marks.
  • Inserting a serial number which represents a date as per Excel’s date system.

Time Functions

Below are the some of the important Excel functions which you can use to deal with time values.

25. TIME Function

Examples For Excel TIME Function

27. HOUR Function

Example-For-Excel-HOUR-Function-Another-Cell-As-A-Reference
Example-For-Excel-HOUR-Function-To-Get-Hour-From-A-Date
Example For Excel HOUR Function To Get Hour From A Date Integer

26. TIMEVALUE Function

Examples For Excel TIMEVALUE Function Simple TIme
Examples For Excel TIMEVALUE Function Full Time Value
Examples For Excel TIMEVALUE Function End Time
Examples For Excel TIMEVALUE Function Date With Time

28. MINUTE Function

Examples For Excel MINUTE Function

29. SECOND Function

Examples For Excel SECOND Function

30. NOW Function

Examples For Excel NOW Function

Lookup Functions

In Excel, there some specific functions which can help you to look up for a value or specific information about a cell or a range of cells.

Below are some of those which you need to know.

31. ADDRESS Function

You can use ADDRESS function to create a cell address by specifying a row and a column number.

In simple words, you need to specify a reference by providing row and column number as an argument, it returns a cell range as a text.

For example, if you give row number 2 and column number 1 it will return A2 in the result.

Syntax
Arguments
  1. row_num A number to specify row number.
  2. column_num A number to specify column number.
  3. [abs_num] Reference type.
  4. [A1] Reference style.
  5. [sheet_text] A text value as a sheet name.
Examples

In the below example, we have used different arguments to get all types of result.

how to use address function in excel examples
  1. With R1C1 reference style
    • Relative reference.
    • Relative row and absolute column reference.
    • Absolute row and relative column reference.
    • Absolute reference.
  2. With A1 reference style
    • Relative reference.
    • Relative row and absolute column reference.
    • Absolute row and relative column reference.
    • Absolute reference.

32. AREAS Function

You can use AREAS Function to check numbers of areas in a reference.

In simple words, it returns that how many areas you have included in a reference.

In the below example, I have referred to 3 cells and the function has returned 3 in the result.

Definition For Excel ISERR Function
Syntax
Arguments
  • reference A Reference to a cell or a range of cells.
Examples

In the below example, I have used areas function to get the number reference in a named range.

Examples For Excel AREAS Function
Important Points
  • If you want to refer to more than a one cell reference, you have to enclose all that references in more than one set of parentheses and use commas to separate each reference from other.
  • Reference can be a cell, a range of cells or a named range.

33. CHOOSE Function

CHOOSE function can help you to get a value from a list based on its position.

In simple words, you can call a value from a list by specifying the position of that value.

For example, if you have created a list of months (Jan to Dec) and if you mentioned position 5 it will return “May” in a result.

In above example, I have used CHOOSE function with an option button to select the type of the service to calculate the shipping cost.

How To Use Excel Choose Function
Syntax
Arguments
  1. index_num A number for specifying the position of the value in the list.
  2. value1 A range of cells or an input value from which you can choose.
  3. [value2] A range of cells or an input value from which you can choose.
Examples

In the below example, I am using CHOOSE Function with a drop-down list to calculate four(sum, average, max, and mix) different things.

I have used below formula to calculate the all four things with CHOOSE Function.

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

Use Excel CHOOSE Function With Drop Down List

I have created a small table with the name of all four calculations which I want and inserted a serial number to each in the corresponding cell.

After that, I have created a drop down list for all four calculations.

Now, to get index number in choose function from that small table I have created a lookup formula which will return serial number as per the value selected in drop down list.

And, instead of values, I have used four formulas for 4 different calculation.

Whenever you select calculation type with drop down list it will change the calculation.

Important Points
  • It can handle up to 254 values.
  • You can refer to a cell or you can also insert values directly in the function.

34. COLUMN Function

You can use COLUMN function to get the column number of a reference.

In simple words, it will return a number which represents the column number of the reference.

For example, if you refer to cell A3 it will return 1 in the result as A’s column number is one.

Syntax
Arguments
  • reference A cell reference for which you want to get the column number.
Examples

In the below example, I have used column function to the get the column number of the cell A1.

Get Column Number With Excel COLUMN Function

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, I have used column function create a header with serial numbers.

Heading Serial Number With Excel COLUMN Function
Important Points
  • You cannot refer to multiple references.
  • If you refer to an array, 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 column number of the cell A1.
  • You skip to specify a reference, it will return the column number of the current cell.

35. COLUMNS Function

You can use COLUMNS function to get the number of columns include in a reference or array.

In simple words, you can get the number of columns which are included in the reference.

For example for reference A1:D1 if will return 4, as a result, because you have 4 columns in that reference A, B, C and D.

examples to understand excel sheet function
Syntax
Arguments
  • array An array or range get from which you want to get the number of columns.

In the below example, I have used columns function to create serial numbers in headers.

I have used fixed cell reference for the first cell in the range and drag it to the left.

Examples

In the below example, I have used columns function to get the number of columns from range A1:F1.

Get Column Numbers With Excel COLUMNS Function
Important Points
  • You can also use named range in columns function.
  • COLUMNS function is not concerned with the values in the cells, it will simply return the number of columns in a reference.

36. FORMULATEXT Function

You can use FORMULATEXT function to get a formula from a cell as a text string.

In simple words, when you refer to a cell which contains a formula, it will return that formula from the cell, like which is displayed in the formula bar if you select that referred cell.

Syntax
Arguments
  • reference A cell reference from which you want formula as a text.
Examples

In the below example, I have used formula text with a different type of references.

When you refer to a cell which doesn’t have any formula, it will return “#N/A” error value

Examples For Excel FORMULATEXT Function
Important Points
  • You can refer to another workbook & another worksheet.
  • 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 result from both.

37. HLOOKUP Function

You can use HLOOKUP to lookup a value from a top row of the data table, and if that value found, you can retrieve a value which is a number of cells below in the same column. In short, for horizontal lookup.

The alphabet H in HLOOKUP means horizontal, and V in VLOOKUP is vertical. In the below table, we have months name in the first row and product name in the first column.

DIfference Between VLOOKUP Function Excel HLOOKUP Function

Let’s say if you want to lookup for “Jun” month from the first row and want quantity value of “Product-5” then you can use HLOOKUP for that.

And, another condition is, If you want to lookup for “Product 4” and want quantity value of “Apr” month then you can use VLOOKUP for that.

Syntax
Arguments
  1. lookup_value A value you want to lookup.
  2. table_array A data table or an array from which you want to lookup value.
  3. row_index_num A numeric value represent 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.
  4. [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 non-exact match use TRUE (Default).
Examples

In the below example, I have used HLOOKUP function with match function to create a dynamic formula.

And, I have used a drop-down list to change the lookup value from the cell.

How To Use Excel HLOOKUP Function With Match Function

I have used below formula to perform a dynamic HLOOKUP formula.

Using-Excel HLOOKUP Function With MATCH Function

I have used zone name from cell C7 as a lookup value. Range B1: F5 as table array. And, for row_index_num I have used match function to get the row number.

Whenever I change the value in cell C9, it will return the row number from 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.

Important Points
  • You can use wildcard characters with HLOOKUP.
  • You can perform an exact match or 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 lookup value exists in lookup range.
  • If range_lookup is true or omitted, and lookup value is not in the lookup range, it will return the nearest value which is less than lookup value.
  • If range_lookup is false, then there is no need to sort data range.

38. HYPERLINK Function

This is the best way to create a hyperlink in excel. Yes, the best way.

You are already familiar with HYPERLINKS in Excel, HYPERLINK function is just like that but with some additional powers.

It’s more convenient, fast, and easy to change or edit.

Syntax
Arguments
  • Link_Location Location for which you want to add a HYPERLINK. It can be further split into two terms.
    1. link: It can be an address of a cell or range of cells in the same worksheet or in any other worksheet or may be in any other workbook. We can also link a bookmark from a word document.
    2. location: It can be a link to a hard drive, a server using UNC path, or any URL from 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 “HTTP://” before a web address.
  • [friendly_name] It is an optional part of this function. It acts as a face of the connected link.
    1. You can use any type of text, number, or both.
    2. You also refer to a cell which contains the friendly_name.
    3. If you skip it, the function will use link address to display.
    4. If friendly_name returns an error, the function will display error.
Examples

In the below example, we have linked to a cell in a file which is stored on OneDrive.

You can use hyperlink function to share files which are stored on OneDrive.

Use Excel HYPERLINK Function To Link To A File In Onedrive

In the below example, I have used hyperlink function to link to a specific range in a file which is saved on the desktop folder on my laptop and when I click on this link, it open that file and navigate to that range.

Use Excel HYPERLINK Function To Link To A Range In Same File
Important Points
  • Link a file saved on a Web Address: You can use a file which 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 which 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.

38. HYPERLINK Function

It happens sometimes when you want to get a value from a list and you know the position of that value from the list. Excel INDEX Function can help you to do that.

By using INDEX Function you can fetch a value from a range of cells in which you know the exact position of the value.

In the below example, we have a month wise quantity.

And, we have used index function to get quantity for the month of Jun as in the list Jun is on 6th.

how to use excel index function get value from a list
Syntax

INDEX Function has two different syntaxes.

In first, you can use array form of index to simply get a value from a list using its position.

Second is reference form which is less used in real life but you can use it if you have more than one range to get value from (I’ll show further, an example to make it understand).

Arguments
  • array A range of cells or an array constant.
  • reference A range of cells or multiple ranges.
  • row_number Number of the row from which you want to get the value.
  • [col_number] 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.
Examples
1. Using ARRAY – Getting Value from a List

In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.

And, I’m only referring to a single column that’s why I have specified 1 in column_number.

In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.

And, I’m only referring to a single column that’s why I have specified 1 in column_number.

how to use excel index function example with arrays

INDEX has returned 1904 in the result.

how to use excel index function get value from a list

And, if you want to get any other value, all you have to do, just mention the position of the value in the list. Whereas, if you referring to a range with more than one column you have to specify the column number.

how to use excel index function example with arrays column row intersect

INDEX will return the value from the intersection of row_number and column_number.

2. Using REFERENCE – Getting Value from Multiple Lists

In below example, instead of selecting all the range in one go, I have selected it as three different ranges.

In the last argument, I have specified 2 in area_number which will define the range to use from these three different ranges.

how to use excel index function using as reference

Now, in the second range, I’m referring to 5th row & 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.

You can also refer to ranges with multiple columns.

Important Points
  • 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!.

Quick Tip: If you use INDEX Function as a range reference [A1:Index(B1:B5,1,1)] it will return cell reference instead of value.

40. INDIRECT Function

It allows you to create a dynamic cell reference by using text instead of actual reference and the best part is when you want to change the reference, you just have to change the text, not the actual reference.

Once you refer to a cell using the indirect function it will evaluate that reference on a real time and return the content of the cell.

The indirect function is one of those functions which are really unique in their usage and, the best part is when you need to use this function it will work like a magic.

Syntax
Arguments
  • 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 cell reference type, it will use A1 style as default.
Examples
1. Reference to Another Worksheet

You can also refer to another worksheet using the indirect function.

For this, you have to insert worksheet name in the indirect function.

how to use excel indirect function to refer to another worksheet

In above example, I have used the indirect function to refer to another worksheet. I have the sheet name in cell A2 and cell reference in cell B2.

In cell C2, I have used the following formula to combine the text.

=INDIRECT(“‘”&A2&”‘!”&B2)

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”.

So, that’s why indirect function 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 workbook name in your text which you are using as a reference.

how to use excel indirect function to refer to another workbook

In above example, I have used below formula to get the value from the cell A1 of the workbook “Book1”.

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

As I have workbook name in cell “A2”, worksheet name in cell “B2” and cell name in cell “C2”. I have combined them to use as an input text in indirect function.

Important 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 function.

how to use excel indirect function with drop down list

In above example, I have a drop down list in cell E1 which has a list of named ranges. And, in cell E2 I have used that name in the indirect function.

As range B2:B5 is named as “Quantity” and range C2:C5 is named as “Amount”. When I select quantity from drop-down indirect function instantly refer to the named range.

When I am selecting quantity from the drop-down, indirect function instantly refers to the named range “Quantity” and return the sum of that range.

And, when I am selecting the amount from the drop-down, I have the sum of cell range C2:C5.

Important Points
  • 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 which has the text you want to use as a reference.

41. LOOKUP Function

You can use the LOOKUP function to search for the position of a value from a column or a row and then return a value from the same position from another column or row.

For example, if a value which you have specified is on 3rd position in a column then this function will return the value which on the 3rd position in the second column.

There are two types of LOOKUP function.

  • Vector Form
  • Array Form

Instead of using array form it’s better to use VLOOKUP or HLOOKUP and, here I have mentioned about vector form and if you want to learn about array from you can check Microsoft’s Help Section.

Syntax
Arguments
  • value A value which you want to search from a column or a row.
  • lookup_range A column or a row from which you want to lookup for the value.
  • [result_range] A column or a row from which you want to return a value. This is an optional argument. If you skip to specify it LOOKUP function will return

Instead of using array form it’s better to use VLOOKUP or HLOOKUP. 

42. MATCH Function

You can use MATCH function to get the position of a cell from a range or an array.

In simple words, you can get the position of a lookup value from a list of values.

Syntax
Arguments
  • lookup_value A value whose position you want to get from a list of values.
  • lookup_array A range of cell or an array contain values.
  • [match_type] A number (-1, 0 & 1) to specify how excel look for the value from the list of values.
Examples

In the below example, I 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 will return the largest value which is equal or smaller than lookup value.

Now, in the entire list, there are 3 values which are smaller than 5 and 4 is the highest in them. And, the result is 3 which is the position of value 4.

examples to understand excel sheet function
Important Points
  • You can use wildcard characters with match function.
  • If there is no matching value in the list if will return #N/A. You can use IFNA Function and IFERROR
  • Function to show a meaningful message.
  • Match function is non-case sensitive.
  • You can use match_type as per below information:
    1. 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.
    2. If you use -1, it will return the smallest value which is equal or greater than the lookup value.
    3. The values in the list must be sorted in ascending order.
    4. If you use 0, it will return the exact match from the list.

sample file

43. OFFSET Function

You can use the OFFSET function to create a reference to a range which is a specific number of rows and columns away from a cell or range of cells.

Syntax
Arguments
  • Reference is the reference from which you want to offset to start. It can be a cell or range of adjacent cells.
  • Rows argument is the number of rows which tells 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 argument is 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] is a number to specify the rows to include in the reference.
  • [width] is a number to specify the columns to include n the reference.
Examples

In the below example, I have used SUM with OFFSET to create a dynamic range which sums the values from all the months for a particular product.

example excel offset function
Notes
  1. OFFSET is a "volatile" function, it is recalculates whenever there is any change to a worksheet.
  2. It displays the #REF! error value if the offset is outside the edge of the worksheet.
  3. If height or width is omitted, the height and width of reference is used.

44. ROW Function

You can use ROW Function to get row number of a reference.

Syntax
Arguments
  • reference - A cell reference or a range of cells for which you want to check the row number.
Examples

In the below example, I have used row function check the row number of the same cell where I have used the function.

Excel For Excel ROW Function Same Cell

In the below example, I have referred to the another cell to get the row number of that cell.

In the below example, I have referred to the another cell to get the row number of that cell.

Excel For Excel ROW Function Another Cell

You can use row function to create a serial number list in your worksheet.

All you have to do just enter row functions in a cell and drag it up to the cell you want to add serial numbers.

Use Excel ROW Function To Insert Serial Numbers