Top 5 Advanced Functions in Excel- Useful Formulae

top 5 advanced function in excel which is very useful formulas

VLOOKUP | IF | AND & OR | IFERROR | CONCATENATETop 5 advanced functions in Excel

These functions are the top 5 advanced functions in excel. These are very helpful or useful to solve real-world problems. These are very complex formulae to learn. we will try to explain with examples.

1-VLOOKUP (Vertical Lookup) in Excel – Top 5 Advanced Functions

Que-What is VLOOKUP in excel and how to use it

Before learning VLOOKUP, it is very important to know that why should we use VLOOKUP

Let’s take a real-world example to assume that you have to retrieve 100 records from thousands of records, so what can you do?

The first option is that you can filter your source table and search 100 records one by one and paste into your working table. This process will take a lot of time and effort. Instead of this, you can use the lookup function that is VLOOKUP.

So, by using VLOOKUP, You can fetch 100 of records at one time. You will not have to use the filter again and again. The below picture is a small example of VLOOKUP that help you to understand that why should we use VLOOKUP.

Vlookup example pic Top 5 advanced functions in Excel
Vlookup Example

By the definition, VLOOKUP searches particular column value regarding known key value and it matches the value from another column and returns the match value corresponding to that row into a working table.

Or

VLOOKUP is a lookup function which is used to fetch the record value from the other column and returns the corresponding match row value into the subjective sheet by using key value.

At least, you have come to know why do we use VLOOKUP exactly. Now the question arise that how will we use VLOOKUP?

To use VLOOKUP, you should know the formula Syntax of VLOOKUP.

=VLOOKUP(LOOKUP VALUE, TABLE ARRAY, COLUMN_INDEX_NUMBER, RANGE LOOKUP)

There are four basic Arguments in VLOOKUP which are given here.

1. Lookup Value – Lookup value is the key value which is a help to find other value Ex. Roll No, ID No

Note – Most cases Lookup Value is always unique in the source table. key value column always in leftmost in the source table.

2. Table Array – Table Array is source tables where all the data exist and key value find the record in the table array.

3. Col_Index_Num – This is a column Number which you want to return a record value of the related field.

4. Range Lookup – Optionally, range lookup has two components true and false where true means approx. match and false means exact match. By default, the value is true if you not specify exact match.

Note:

There are always a minimum two tables while using VLOOKUP, one is the source table where all the data exist and second is working table where you want to access or retrieve some data from the source table. VLOOKUP function helps you to access or retrieve data from the source table to the working table.

VLOOKUP EXAMPLE

Here, we will discuss another example to learn how to use VLOOKUP. In the following example, we have two tables; one is the source table that has information about students. (Roll no, name, class, subject, marks)

The second table is a working table where the table has only the students’ Roll Number. We want to retrieve or fetch student information details from source table by using the key values i.e. roll number. To get the student information details we use VLOOKUP.

1.    At first, you create the following tables, one is a source table and the other is the working table.

Vlookup example pic 1

2. Define name range for the source table. To define name range of source table, you will select the source table and give any name of the source table in the name box which is situated in over on the top left of your Excel screen or below the ribbon tab. In our example, we are giving the table name: tablex.

Vlookup example pic 2

3.    Now you move to the working table and select cell H3 and type the formula to retrieve: =VLOOKUP(G3,tablex,2,FALSE)

Vlookup example pic 3
Where G3 is lookup value and tablex is a source table name and 2 is a column number which you want to fetch and false is an exact match.

4.    Press Enter on your keyboard. The formula will be evaluated, and the result will be shown in the cell.

Vlookup example pic 4

5.    Select H3 cell and keep pressing small plus sign + in the bottom-right corner of H3 cell and drag the cell downwards which you want to fill. In an example, we will drag H3 to H8.

Vlookup example pic 5

6.    When you release the mouse left key you will find that all the value has come corresponding to students’ roll number.

Vlookup example pic 6

7.    You can use the same method to retrieve data for all the columns. Everything will be same in the formula except the column number; by changing the column number you can get result from the related column.

Vlookup example pic 7 Top 5 advanced functions in Excel

It is a very basic example of VLOOKUP. You should use this example from another sheet to the active sheet and also from another workbook sheet to the active workbook sheet.

2 – IF – Top 5 Advanced Functions in excel

IF function is a logical function which gives two outcomes either true or false based on the logical test.

Or

IF function is a conditional function which performs a logical test and return one value for true outcomes and another for false outcomes.

Before learning IF, it is very important to know, what are the logical operators? The given list helps you to understand the logical operators.

Relational operators in excel Top 5 advanced functions in Excel
Relational Operators

IF function Syntax

=IF (logical_test, [value_if_true], [value_if_false])

Arguments

Logical Test – This is a logical part where you can give one or more condition by using a logical operator or operand.

value_if_true – you can keep any value and this value will return when logical test evaluates to true.

value_if_false – you can keep any value and this value will return when logical test evaluates to false.

Example of IF function

In this example, we want to evaluate “pass” or “fail” condition, if the student has 50% or more than 50% then he or she will be passed otherwise failed.

if function example pic
IF Function

The result will change automatically when we change the percentage of the student.

Nested IF or multiple IF statements

Nested IF has multiple conditions, Means we can evaluate multiple logics in nested IF. There are multiple conditions with multiple results but Nested IF returns only single answer instead of multiple results.

This formula contains multiple conditions so beware by using nested-if formula.

In the following picture, we have performed multiple conditions, by using nested-if function, we will evaluate the student result.

nested if function example pic Top 5 advanced functions in Excel
Nasted IF Example

The result will change automatically when we change the percentage of the student.

3 – AND & OR – Top 5 Advanced Functions in excel

AND or OR functions is nothing if we are using alone but if we are using AND or OR function with IF or any other functions then these are the very powerful functions.

It is very important to know what these functions are and how to use it?

AND or OR functions are logical functions which can return only one outcome either TRUE or FALSE based on condition. These functions have one or multiple conditions and then evaluate multiple conditions and return either TRUE or FALSE.

So At first, we will discuss AND function

AND function is a logical function which can have one or multiple conditions and returns TRUE when all the conditions are TRUE otherwise it returns FALSE.

To use AND Function, you should know the formula Syntax of AND function.

=AND (LOGICAL 1, [LOGICAL 2], [LOGICAL 3] , …)

Example of AND function

In this example we will evaluate is student pass or fail? if the student passes the exam, the answer is TRUE otherwise FALSE. Both conditions are given below.

1. The student has five subjects and each subject passing marks is 50 and above in 100 marks.

2. Total marks in five subjects must be 300 and above in 500 marks means the student has to score 60% and above of his or her examination.

The Student has 5 subjects which are English, art, math, physics, and chemistry. Each subject is 100 marks and the student will have to get marks 50 and above to pass the exam and also the student must have marked 60% or above in total marks 500.

and function in excel pic Top 5 advanced functions in Excel
and function example pic

The result will change automatically when we change the percentage of the student.

Next, we will discuss OR Function

OR function is a logical function which can have one or multiple conditions and returns FALSE when all conditions are false otherwise it returns TRUE if any of the condition is true.

OR

The function returns TRUE If any of the conditions are true otherwise it returns FALSE if all the conditions are false.

To use OR Function, you should know the formula Syntax of OR function.

=OR (LOGICAL 1, [LOGICAL 2], [LOGICAL 3] , …)

Example of OR function

In this example, we will evaluate the salesperson price money. If the salesperson achieves his target of any month of 1st Quarter then he is eligible for the incentive otherwise he is ineligible.

The condition is given below

The salesperson target is 50 cartons. If salesperson sale 50 cartons or above in any month of quarter 1st then he is eligible for the incentive amount which is 25000/- Rs.

Here if salesperson achieves the target then the answer is TRUE otherwise FALSE.

or function example pic
or function example pic

Achievement value will change automatically when we change any month sale of the salesmen.

4 – IFERROR – Top 5 Advanced Functions in excel

IFERROR function is very important function this function is helped to ignore the error of your formula and you can give any value instead of an error.

Sometimes we see the error and think how to handle them so by using this function you can put any value and hide the error message.

There is lots of error in MS Excel like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! And now we will see how to hide or ignore error value in the formula.

IFERROR Example

 In this following example, we have some value in the first and second column and we want to perform division operation from the second column to the first column value.

iferror example pic

When any value divisions by zero ‘0’ then the formula gives an error, to manage error value we will use IFERROR formula.

iferror exaple pic 2 Top 5 advanced functions in Excel

So, in above example we replaced error #DIV/0! value with ‘0

5 – CONCATENATE – Top 5 Advanced Functions in excel

Concatenate is a text function which is used to combine data from two or more data cells into one cell.

We can join multiple string cells into one cell by using the concatenate function. We can also use concatenate operator ‘&’ to join or combine multiple cells into one cell.

CONCATENAME Example

In the following example, we will join multiple data column by using concatenate function and also we can use concatenate operator ‘&’.

concatenate example pic Top 5 advanced functions in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

shares