## 1. IF Function

**IF Function returns a value if the condition you specify is TRUE, else some other value.** In simple words, the IF function can test a condition first and returns a value based on the result of that condition.

### Syntax

IF(logical_test,value_if_true,value_if_false)

### Arguments

**logical_test:**The condition which you want to evaluate.**value_if_true:**The value which you want to get if that condition is TRUE.**value_if_false:**The value which you want to get if that condition is FALSE.

### Notes

- The maximum number of nested conditions you can perform is 64.
- You can use comparison operators to evaluate a condition.

### Example

In the below example, we have used a comparison operator to evaluate different conditions.

- We have used a specific text to get in the result if the condition met or not.
- You can also use TRUE and FALSE to get in result.
- If you skip specifying a value to get the result if the condition is TRUE, it will return zero.
- And if you skip specifying a value to get the result if the condition is FALSE, it will return zero.

In the below example, we have used the IF function to create a nesting formula.

We have specified a condition and if that condition is false then we have used another IF to evaluate another condition and perform a task and if that condition is FALSE we have used another IF.

In this way, we have used IF five times to create a nesting formula. You can use the same for 64 times for a nesting formula.

## 2. IFERROR Function

**IFERROR function returns a specific value if an error occurs.** In simple words, it can test value and if that value is an error, it returns the value you have specified.

### Syntax

*IFERROR(value, value_if_error)*

### Arguments

**value:**The value you want to test for the error.**value_if_error:**The value which you want to get in return when an error occurs.

### Notes

- IFERROR function is concerned with the occurrence of an error, not with the type of the error.
- If you skip specifying value or value_if_error, it will return 0 in the result.
- It can test #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
- If you are evaluating an array it will return an array of results for each item specified.

### Example

In the below example, we have used the IFERROR function to replace the #DIV/0! with some meaningful text.

IFERROR is only compatible with 2007 and earlier versions. To deal with this problem, you can use ISERROR.

## 3. TRUNC Function

TRUNC Function **returns an integer after truncating the original number.** In simple words, it removes the decimals from a number to a specific precision and then returns the integer part of the result.

**Syntax**

*TRUNC(number, [num_digits])*

**Arguments**

**number:**The number you want to truncate.**[num_digits]:**A number to specify precision to truncate a number.

**Notes**

- If you skip specifying multiple it will return with an error.
- It rounds away from zero.
- If you have two multiples on the same distance it will return the multiple which is higher than the number, you are rounding.

### Example

In the below example, we have used TRUNC to truncate data for removing time from the dates.

## 4. SUMIF Function

SUMIF Function returns the **sum of the numbers which meet the condition you specify**. In simple words, it only considers and calculates the sum of values that fulfill the condition.

**Syntax**

*SUMIF(range, criteria, [sum_range])*

**Arguments**

**range:**A range of cells from which you want to check for criteria.**criteria:**A criteria which can be a number, text, expression, cell reference or a function.**[sum_range]:**A cell range that has the values you want to sum.

**Notes**

- If the sum_range is omitted, the cells in the range will be summed.
- Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, which must be enclosed in double quotation marks.
- The size of the criteria range and sum range should be of the same size.

### Example

In the below example, we have specified A1:A9 as the criteria range and B1:B9 as the sum range and after that, we have specified the criteria in A12 which has the value C.

You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion that has an alphabet “S”.

And, if you skip specifying the sum range it will give you the sum of the criteria range. But, that will be only possible if the criteria range has numeric values.

## 5. 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.

**Syntax**

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])

**Arguments**

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

**Notes**

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

### Example 1 – Using ARRAY to Get 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.

### Example 2 – Using REFERENCE to Get 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.

## 6. 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.

**Syntax**

*VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)*

**Arguments**

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

**Notes**

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

### Example

#### 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:

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

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

## 7. IFNA Function

**IFNA function returns a specific value if an #N/A error occurs.** Unlike IFERROR, it only evaluates the #N/A error and returns the value you specified.

**Syntax**

IFNA(value, value_if_na)

**Arguments**

**value:**The value you want to test for #N/A error.**value_if_na:**The value you want to return if an error occurred.

**Notes**

- If you skip specifying any argument, IFNA will treat it as an empty string (“”).
- If a value is an array then it will return the result as an array.
- It will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

### Example

In VLOOKUP function, #N/A occurs when the lookup value is not in the lookup range and for that we have specified a meaningful message using IFNA.

**Note:** IFNA is introduced in Excel 2013 so it is not available in the previous versions.

## 8. RAND Function

**The RAND function returns a random number ranging from 0 to 1.** In simple words, you can generate a random number between 0 to 1 (it updates its value every time you make a change in the worksheet).

**Syntax**

*RAND()*

**Arguments**

- There is no argument to specify in RAND functions

**Notes**

- If you put zero in multiple it will return zero in the result.
- If you skip specifying multiple it will return with an error.
- It rounds away from zero.
- If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

### Example

Apart from having numbers between 0 and 1, you can also use RAND for random numbers between two specific numbers. In the below example, I have used it to create a formula that generates a random number between 50 and 100.

When you enter this formula in a cell it returns a number between 100 and 50 by multiplying the values returned by the RAND with the equation we have used. To understand this formula we need to split it into three parts:

- First of all, when it detects the lowest number from the highest number you get the difference between both of them.
- Then secondly it multiplies that difference with the random number returned after the subtraction.
- And third, add that number with the lowest number left in the third part of the equation.

## 9. SUM Function

SUM function returns the **sum of the values** supplied. In simple words, with the SUM function, you can calculate the sum of a list of values (you can directly input value into the function or refer to a range of cells.

**Syntax**

*SUM(number1,[number2],…)*

**Arguments**

**number1**: A number, a range of cells that contain numbers, or a single cell that contains a number.**[number2]**: A number, a range of cells that contain numbers, or a single cell that contains a number.

**Notes**

- It ignores text values.

### Example

In the below example, you can insert numbers directly into the function by using commas between them.

You can also simply refer to a range for calculating the sum of the numbers and if there is a text, logical value, or empty cell it will ignore them.

If there is an error value in a cell that you are referring to, it will return #N/A in the result.

If you have numeric values that are formatted as the text it will ignore them. It’s recommended to convert them into numbers before using SUM.

## 10. OR Function

**OR Function returns a Boolean value (TRUE or FALSE) after testing conditions you specify.** In simple words, you can test multiple conditions with AND function and it returns TRUE if any of those (or all) conditions is TRUE and returns FALSE only if all those conditions are FALSE.

**Syntax**

OR(logical1, [logical2], …)

**Arguments**

**logical1:**Condition which you want to verify.**[logical2]:**Additional Conditions you want to verify.

**Notes**

- Values will be ignored if the reference cell or array contained an empty cell or text.
- The result of conditions should be in logical value (TRUE or FALSE).
- It will return an error if there is no logical value is returned.

### Example

In the below example, we have created a condition using IF function that if a student score 60 above marks in one of the both of the subjects the formula returns TRUE.

Now in the below example, we have used a number to get logical values in a formula. You can also perform the above condition in reverse order.

You can use TRUE and FALSE instead of numbers. OR function treats these logical values as numbers.

## More Tutorials

**Statistical Functions** / **Date Functions** / **String – Text Functions** / **Financial Functions**

interested in electronic math to mechanical conversion

I fancy EXCEL & where ever possible I try to programe it. PLEASE help me with all the functions & their examples. Have done it for Numerology, Family genealogy, now Family trust fund am stuck with the “AND” function of using it. Please help me.83 years age. NO Website, wish I can develop one.

Where do you get stuck?

Where do you get stuck?

This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

Please help. I have a column with a title.

If it has been checked how to I copy the title to another cell?

Thank you very much

Thank you sir I must so help ful.

=search(“9″,”99 9000”) = result is 1

How about another “9” position? What is formula?

Sir, 2nd Sept.2019.

Really you have shown very clearly and deeply all points.

I must thank you.

Hoping to receive more articles in future too.

Kanhaiyalal Newaskar.

Thank you for your generosity.

NICE, REALLY HELPFUL

Thank u

Thank you for working hard to help others. Your generous effort will be put into good use.

Thank you for your hard work for helping out us…

Use RANK function

This is amazing and very generous of you…many thanks…

You’re Welcome 🙂

This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

Lavoro ottimo e ++ utile. Grazie e complimenti

nice information

we are here because you and your work is awesome. 😉

Keep on sharing. This help us a lot. Its a time save for us and no need to search each functions from net cause its already here.

Hi, I find your Excel tips very useful. It helps me a lot. Tips likes this give me a lesser time to search for the uses and functions of each formulas. Thanks a lot.

Very useful information..thank you sir

Sir, very useful information,notes and formula you have given.User like me would appreciate the work.

Once again thanks.

Thank you very much Mr. Punit Sir. I am computer faculty and recently I have asked to teach Excel functions and I find the complete list here. Could you possibly provide us the PDF format of all the functions with examples to use. Thank you.

Thanks a lot for sharing your knowledge with us with clear explanation. Your blog is my guru. All your excel tips are being very useful for my work. Thanks again.

GDay Punit

Needed a refresher, thanks for the info.

First of All.. Thank you for sharing the above info.. I fully appreciate this gesture..

A honest effort with intention of educating…. Really Super …………..

Hello Punit,

I am very thankful to you as you have start this learning portal for all. And thanks for giving ur stuff from this web portal i will be able to use excel smartly and efficiently. I learn so far with this really Excellent work ur doing

Thanks a lot again.

Thank you 🙂

Dear Puneet thanks, i want learn Macro in detail, can you help me

Thanks

Nitin

Please check each category. some files are not there or linked wrong

Dear Puneet,

Excellent work. Many files are not working or the link is wrong.

Thanks Rashid

Dear Puneet,

Excellent work. Many files are working or the link is wrong.

Thanks Rashid

Hi Puneet,

I want to learn macro VBA step by step please help me.. you are doing really great job

Thanks a lot.

Thank you so much puneet. It is very nice tutoral.

Its vry usefull. Nice info.

Its vry usefull

Thank you so much Puneet Gogia and nice to see the whole functions list in online,it is helpful to me.I’m really appreciate to you… i want to learn VBA pls need you guideline (confusion in coding)

Hi, Dear Punit,

I am very thankful to you as you have start this learning portal for all. In my office people are jealous they don’t give me chance to learn excel and no one guide me how to use excel smartly even though many of them are working smartly in excel. But from your this web portal i will be able to use excel smartly and efficiently. Thanks a lot again.

(Maraming salamat po sayo Puneet..) Thank you so much Puneet. It’s a great help for me to learn more from you

You’re Welcome.

Thank you so much

It will be better if you put more information about Excel macros. Thank you for your contributions in order to make people’s working lives easier. Keep up the good work.

my self deepak,

thanks for this, good job

kindly provide advance vba/excle programes

Great Puneet.. You made wonderful work to make everyone expert in Excel.

I want to have an example as well after explaining

after long time i find such amazing summary about excel function …thanxs alot sir

Gogia sir, plz suggest solution, if any

Sir it was done manually just to let you explain my actual need, please suggest function, if you can help in this regard.

https://uploads.disquscdn.com/images/0702942c4aa7355dffa9637ed2508ef37a5666306d6e3db4491e61dd4f600f9d.jpg there are 25 row in a column in which numbers are stored in-sequential like 0, 32,1,5,15,20,101,0,15,0,2,3,0………….. one figure in every one row, I need to allocate one serial number for all the duplicates that too in ascending order of the stored in-sequential figure, I want to apply sorting smaller to larger

You have already sorted in above snapshot?

please give formulas and function in the pdf formet

Thanks Puneet for your support

Very useful tips like camera option, Picture graph thanks a lot

Thanks a lot for providing a helpful resources. You did a excellent job. Thanks again…

Are these available in a PDF or Word File? This is a good reference, but it’s hard to remember to come back here for the information. Having them as something you can download would be really helpful.

How to get data of various sheets on Summarry sheet of the inventory register.

Thank you for the valuable information…I would like to know one specific thing.

Once we use the transpose, only values are copied. If i want to retain the values with formula in transpose function, is it possible. This is very important to keep scientific way to prove that the transpose operation done is correct. In data analytics big excel sheets are handled and a small mistake can lead to big problems .. Please advise me ..

How Can I creat a daly sale report in excel

please help me

Puneet, you are simply amazing.

Thanks It is very useful to know which formula where to use. Thanks Puneet Bro

Hi,

Please provide the link for Inv. Management System

very nice useful puneet how i download this excel function book

How can I create a timezone convertor??

Thanks punnet for providing such a good platform to learn excel

MS has already created a summary lists of all functions with links to brief descriptions.

functions (alphabetical) 2016-2007

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

Functions (by category) – 2016 – 2007

https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks

Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome

I’d love it.but it will be nice if you combine it in a one PDF file 🙂

HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)

Try,

=TEXT(10:01-09:42,”HH:MM:SS”)

SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.

Thanks Dan.

GETPIVOTDATA, I haven’t nailed that one yet…dan.kordelski@gmaildotcom

OFFSET and exact use of Array with detailed example.

Thanks Mohammed Mubin Sayyed

Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.

Yeah Iyek. I am trying to give two different examples. one is solo & other is combined with some other function.

Great! Puneet. I am looking forward to this.

OFFSET/MATCH AND ROW/COLUMN

Thanks Iyke.

Index or match

Thanks Angel

I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.

I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.

Thanks Srinivasan

Offset plays a vital role

Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!

Alright Diana. Thanks for your reply.

Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!

Thanks Brenda. I will for sure.

You’ll get your E-book on 30 Apr

i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)

Hey Torstein,

You will get the both.

Thanks for words.

You’ll get your E-book on 30 Apr

Hi, Can you add the sumproduct function. I want to know how you use it to bring back sums based on 3 or conditions. Thanks eannablack@gmail.com

Hello Eanna,

Already Added.

Thanks for your comment. You’ll get your E-book on 30 Apr

very helpful, i really appreciate. keep it up Bro

Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.

Great to have such a summary available

Thanks Inet