Excel Advanced Formulas (Table of Contents)
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 the 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.
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.
However, using asterisk character at either end of the lookup value we can fetch the data for partial lookup value.
In table 1 I have full name and salary, but in table 2 I have only partial names and need to find the salary of each employee.
Step 1: Open the VLOOKUP formula in cell E3. Before selecting the lookup value put an asterisk (*) on either side of the lookup value.
Step 2: As usual you can complete the VLOOKUP formula now and we will have the results.
After using the above formula the output is shown below.
4.9 (3,676 ratings)
View Course
The same formula is used in other cells.
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 (=).
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?
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 bonus based on multiple conditions then you need to nest AND or OR condition with 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.
Apply below formula to get the bonus amount.
After using the above formula the output shown below.
Same formula applying in cell E3 to E9.
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 (&).
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 is the best or highest salesperson in the list? Of course, we have several other techniques to tell the result but this function can return the highest salesperson from the lot.
Apply below function to get the highest salesperson name.
After using the above formula the output shown below.
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.
Apply below formula to get unique values list.
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 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.
Now go to drop down cell and open drop-down dialogue box.
In source press F3 key it will show all defined names, select the name of your drop down list.
Ok, a drop-down list is ready and it will update the values automatically when there is a change in drop-down list range.
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 are tedious task. But we can get rid of these error values by using IFERROR function in the formula.
After using the above formula the output shown below.
Same formula used in other cells.
Example #9 – Use PMT Function to Create Your Own EMI Chart
Nowadays the EMI option is not a strange thing for all us. In excel we can estimate our own EMI chart by using PMT function. Follow the below steps to create your own chart.
Apply below formula in cell B4 to get the EMI amount.
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 INDEX + MATCH function serves as an alternative to VLOOKUP function.
After using the above formula the output is shown below.
The same formula is used in other cells.
Based on the Product ID we need to extract sales values. In the main table 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 how to use Advanced Formulas in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
