 # Top 5 Advanced Functions in Excel- Useful Formulae

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.

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.

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.

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

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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 ‘&’.