EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Formula and Functions Advanced Formulas in Excel
Secondary Sidebar
Excel Functions
  • Excel Formula and Functions
    • Compatibility Mode in Excel
    • VLOOKUP Names
    • Advanced Formulas in Excel
    • Excel Function for Range
    • Excel Template for Payroll
    • Excel Sum Time
    • Payroll in Excel
    • Complex Formulas in Excel
    • Pivot Chart in Excel 2016
    • Advanced Excel Formulas
    • Best Basic Excel Formulas
    • Use Excel As Your Calculator
    • Print Command Button in Excel
    • Excel SUM MAX MIN AVERAGE
    • Excel Basic Functions For Beginners
    • Interactive Excel Dashboard
    • Features of 2016 Excel Workbook
    • What's New In Excel 2016
    • Microsoft Excel Skills
    • Microsoft Excel Shortcuts
    • Microsoft Excel Tips and Tricks
    • Excel Skills
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel Training
  • Excel Advanced Training
  • Excel Data Analysis Training

Advanced Formulas in Excel

By Jeevan A YJeevan A Y

Advanced Formulas in Excel

Excel Advanced Formulas (Table of Contents)

  • Introduction to Advanced Formulas in Excel
  • Examples of Advanced Formula in Excel

Introduction to Advanced Formulas in Excel

Once you reach the intermediate level in excel, you need to work hard to progress to the advanced level. In order to progress to the advanced level, you must be aware of some of the frequently used advanced formulas. In this article, I will cover the top 10 advanced formulas all excel learners must know. Follow this article to learn and explore.

Examples of Advanced Formula in Excel

Let’s understand how to use the Advanced Formulas in Excel with some examples.

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

Example #1 – Partial VLOOKUP Function

You must have encountered a situation where VLOOKUP throws up an error even if there was a slight miss-match in the lookup value. For example, if you are finding for the salary of the name Abhishek Sinha and if you have only Abhishek, then VLOOKUP cannot fetch you the data.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

However, using an asterisk character at either end of the lookup value, we can fetch the data for the partial lookup value.

Advanced Formulas in excel 1-1

In table 1, I have the full name and salary, but in table 2, I have only partial names and need to find each employee’s salary.

Step 1: Open the VLOOKUP formula in cell E3. Before selecting the lookup value, put an asterisk (*) on either side of the lookup value.

Advanced Formulas in excel 1-2

Step 2: As usual, you can complete the VLOOKUP formula now, and we will have the results.

Advanced Formulas in excel 1-3

After using the above formula, the output is shown below.

Advanced Formulas in excel 1-4

The same formula is used in other cells.

VLOOKUP advanced 1

NOTE: One limitation here is Partial VLOOKUP returns the same value if there are Abhishek Sinha & Abhishek Naidu. Because here, the common partial value is Abhishek.

Example #2 – COUNTIFS with Operator Symbols

You must have used COUNTIF & IFS function to count the things in the list. We can also count based on operator symbols like greater than (>) less than (<) and equal sign (=).

Advanced Formulas in excel 2-1

Now take a look at the below data, for example. If you want to count the total number of invoices for the region SOUTH after the date 10th Jan 2018, how do you count?

Advanced Formulas in excel 2-2

Example #3 – IF Condition with AND & OR Conditions

Logical functions are a part of our day to day activities. You need to master them to progress to the next level. If you are calculating a bonus based on multiple conditions, you need to nest and/ or the condition with the IF condition to get the job done.

Assume you need to calculate the bonus amount for each department based on the department and years of service you need these functions. Based on the below criteria’s we need to calculate the bonus.

If service is above 4 years and the department is either Sales or Support bonus is 50000 or else bonus is 25000.

Advanced Formulas in Excel 3-1

Apply the below formula to get the bonus amount.

Advanced Formulas in Excel 3-2

After using the above formula, the output is shown below.

Advanced Formulas in Excel 3-3

Same formula applying in cell E3 to E9.

Advanced Formulas in Excel 3-4

Example #4 – TEXT Function to make you Heading Dynamic

Let’s say you are maintaining a daily sales table, and you have to update the table every day. At the beginning of the table, you have one heading which says “Consolidated Sales Data From DD-MM-YYYY to DD-MM-YYYY”. As and when the table updates, you need to change the heading date. Isn’t it a frustrating task to do the same thing again and again? We can make this heading dynamic by using TETX, MIN, and Max function together with concatenate operator symbol ampersand (&).

Consolidate 1

Example #5 – INDEX + MATCH + MAX to Find the Highest Sales Person

You have a list of Sales Persons and the Sales they made against their name. How do you tell who the best or highest salesperson on the list is? Of course, we have several other techniques to tell the result, but this function can return the highest salesperson from the lot.

Apply the below function to get the highest salesperson name.

INDEX + MATCH + MAX 5-1

After using the above formula, the output is shown below.

INDEX + MATCH + MAX 5-2

Example #6 – Get a Count of Unique Values from the List

If you have many duplicate values and are required to tell how many unique values are there in how do you tell? You can tell by removing the duplicate value, but this isn’t the dynamic way of telling the unique value count.

Using this array function, we can tell the unique values from the lot.

Count of Unique Values 6-1

Apply the below formula to get unique values list.

Count of Unique Values 6-2

Note: This is an array formula; you need to close the formula by holding the Shift + Ctrl key and press Enter key.

Example #7 – Use Named Range to Make the Dropdown Dynamic

If you often work with a drop-down list and make the drop-down list up to date, you need to go back to the source list range to delete or add values. After that, you need to come back to the drop-down cells and again update the range of the drop-down list.

However, if you can create a named range for your drop-down list, you can make the drop-down list dynamic and up to date.

Create a Name Range as shown in the below image.

OFFSET 1

Now go to the drop-down cell and open the drop-down dialogue box.

Data Validation 1

In the source, press the F3 key; it will show all defined names and select your drop-down list.

Data Validation 2

Ok, a drop-down list is ready, and it will update the values automatically when there is a change in the drop-down list range.

Drop down list

Example #8 – Get Rid of Error-Values using IFERROR Function

I am sure you have experienced error values while working with VLOOKUP, Division calculations. Handling these error values is a tedious task. But we can get rid of these error values by using the IFERROR function in the formula.

IFERROR Function 8-1

After using the above formula, the output is shown below.

IFERROR Function 8-2

Same formula used in other cells.

IFERROR Function 8-3

Example #9 – Use PMT Function to Create Your Own EMI Chart

Nowadays, the EMI option is not a strange thing for all of us. In excel, we can estimate our own EMI chart by using the PMT function. Follow the below steps to create your own chart.

PMT Function 9-1

Apply the below formula in cell B4 to get the EMI amount.

PMT Function 9-2

Example #10 – INDEX + MATCH as an Alternative to VLOOKUP Function

I hope you are aware of the limitation of the VLOOKUP function. One major limitation is VLOOKUP can fetch the data from left to right, not from right to left. Not all the time data is well organized and ready to use. Often the value column we are looking for is there on the left-hand side of the lookup value, so VLOOKUP fails to help in these cases.

A combination of the INDEX + MATCH function serves as an alternative to the VLOOKUP function.

INDEX Match 10-1

After using the above formula, the output is shown below.

INDEX Match 10-2

The same formula is used in other cells.

INDEX Match 10-3

Based on the Product ID, we need to extract sales values. In the main table, the Product ID is on the right side of the sales column. So VLOOKUP cannot fetch the data. Use the below formula to fetch the data.

Recommended Articles

This is a guide to Advanced Formulas in Excel. Here we discuss using Advanced Formulas in Excel and practical examples, and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel INDEX Function
  2. Database Function in Excel
  3. TRIM Formula in Excel
  4. Excel OFFSET Formula
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Advanced Formulas Excel Template

EDUCBA

Download Advanced Formulas Excel Template

EDUCBA

डाउनलोड Advanced Formulas Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more