WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » Advanced Excel Formulas

Advanced Excel Formulas

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

List of Top 10 Advanced Excel Formulas & Functions

You can download this Advanced Excel Formulas Template here – Advanced Excel Formulas Template

#1 – VLOOKUP Formula in Excel

This advanced excel function is one of the most used formulae in excel. It is mainly due to the simplicity of this formula and its application in looking up a certain value from other tables, which has one common variable across these tables. Suppose you have two tables with details about a company’s Employee salary and name with Employee ID being a primary column. You want to get the salary from Table B in Table A.

Advanced Vlookup Example 1

You can use VLOOKUP as below.

Advanced Vlookup Example 1-1

It will result in the table below when we apply this advanced excel formula in other cells of the Employee Salary column.

Advanced Vlookup Example 1-2

Drag the Formula to the Rest of the cells.

Advanced Vlookup Example 1-3

There are three  major delimitations of VLOOKUP:

  1. You cannot have a primary column in the right of the column for which you want to populate the value from another table. In this case, the Employee Salary column cannot be before Employee ID.
  2. In the duplicated values in the primary column in Table B, the first value will get populated in the cell.
  3. If you insert a new column in the database (Ex Insert a new column before Employee Salary in Table B), the output of the formula could be different based on a position which you have mentioned in the formula (In the above case, the result would be blank)

#2 – INDEX Formula in Excel

This advanced excel formula is used to get the value of a cell in a given table by specifying the number of rows, columns, or both. Ex. To get the name of an employee at the 5th observation, Below is the data.

Advanced INDEX Example 2

We can use the advanced excel formula as below:

Advanced INDEX Example 2-1

The same INDEX formula can be used in getting value along the row. When using both row and column number, the syntax would look like something like this:

Advanced INDEX Example 2-2

The above formula would return “Rajesh Ved.”

Advanced INDEX Example 2-3

Note: If you insert another row into the 5th row, the formula will return “Chandan Kale.” Hence, the output would depend on any changes happening in the data table over time.

Advanced INDEX Example 2-4

#3 – MATCH Formula in Excel

This Excel advanced formula returns the row or column number when there is a specific string or number in the given range. In the below example, we are trying to find “Rajesh Ved” in the Employee Name column.

The Formula would be as given below:

Advanced Match Example 3

The MATCH function would return 5 as the value.

Advanced Match Example 3-1

The 3rd argument is used for the exact match. You can also use +1 and -1 based on your requirements.

Note: One can combine INDEX and MATCH to overcome the limitation of VLOOKUP.

#4 – IF AND Formula in Excel

There are many instances when one needs to create flags based on some constraints. We all are familiar with the basic syntax of IF. We use this advanced excel IF function to create a new field based on some constraint on an existing field. But what if we need to use multiple columns into consideration while creating a flag. Ex. In the below case, we want to flag all the employees whose salary is greater than 50K but employee ID greater than 3.

Advanced IF AND Example 4

We would use IF AND in such cases. Please find below the screenshot for the same.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

Advanced IF AND Example 4-1

It would return the result as 0.

Advanced IF AND Example 4-2

We can have many conditions or constraints to create a flag based on multiple columns using AND.

#5 – IF OR Formula in Excel

Similarly, we can also use the OR function in excel instead of AND if we need to satisfy one of the conditions of many.

Advanced IF OR Example 5

In the above cases, if any of one condition gets satisfied, we will have cell populated as 1 else 0. We can also substitute 1 or 0 by some substrings with double quotes (“ ”).

Advanced IF OR Example 5-1

#6 – SUMIF Formula in Excel

In some analyses, you might need to filter some observations when applying the sum or count function. In such cases, this advanced excel SUMIF function in excel is at our rescue. It filters all the observations based on certain conditions given in this advanced excel formula and sums up them. Ex. What if we want to know the sum of salaries of only those employees who have employee ID greater than 3.

By Applying the SUMIFS Formula:

Advanced SUMIF Example 6

The Formula returns the results as 322000.

Advanced SUMIF Example 6-1

We can also count the number of employees in the organization having employee ID greater than 3 when using COUNTIF instead of SUMIF.

#7 – CONCATENATE Formula in Excel

This excel advanced function is one of the formulae that can be used with multiple variants. This advanced excel formula helps us to join several text strings into one text string. Ex, If we want to show employee ID and Employee Name in a single column.

Advanced Concatenate Example 7

We can use this CONCATENATE formula to do that.

Advanced Concatenate Example 7-1

The above formula will result in “1Aman Gupta”.

Advanced Concatenate Example 7-2

We can have one more variant by putting a single hyphen between ID and NAME. Ex. CONCATENATE(B3,”-“,C3) will result in “1-Aman Gupta”. We can also use this into VLOOKUP when LOOKUP in excel value is a mixture of more than one variable.

Advanced Concatenate Example 7-3

#8 – LEFT, MID, and RIGHT Formula in Excel

We can use this advanced excel formula to extract a specific substring from a given string. It could be used based on our requirements. Ex. If we want to extract the first 5 characters from Employee Name, we can use the LEFT formula in excel with the column name and second parameter as 5.

Advanced Left Example 8

The Output is given below:

Advanced Left Example 8-1

The application of the RIGHT formula in excel is also the same. It’s just that we would be looking at the character from a right of the string. However, in the case of a MID function in excel, we must give the starting position of the required text string and length of the string.

#9 – OFFSET Formula in Excel

This advanced excel function with a combination of other functions such as SUM or AVERAGE, could give a dynamic touch to the calculations. It is best used in cases when we are inserting continuous rows into an existing database. OFFSET Excel provides us with a range where we need to mention reference cell, number of rows, and columns. Ex. If we want to calculate the average of the first 5 employees in the company where we have a salary of employees sorted by employee ID, we can do the following. The below calculation will always give us salaried.

Advanced OFFSET Example 9

  • It will give us the sum of salaries of the first 5 employees.

Advanced OFFSET Example 9-1

#10 – TRIM Formula in Excel

This advanced excel formula is used to clean up the unimportant spaces from the text. Ex. If we want to remove spaces at the beginning of some name, we can use it by using the TRIM function in excel as below:

Advanced TRIM Example 1

The resultant output would be “Chandan Kale” without any space before Chandan.

Advanced TRIM Example 10-1

Recommended Articles

This has been a guide to Advanced Formulas in Excel. Here we discuss the Top 10 Most Useful Advanced Formulas & Functions in Excel, including VLOOKUP, INDEX, MATCH, SUMIF, CONCATENATE, etc. with a downloadable excel template. You may learn more about excel from the following articles –

22 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>