Advanced Excel Formulas

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

What Are Advanced Excel Formulas?

Advanced Excel Formulas are the formulas that are not so commonly used. These are inbuilt formulas in Excel that are used to retrieve specific datafrom an existing dataset which might be like duplicating the data, filtering w.r.t specific criterias, conditional formulas, etc. These functions are used to generate reports, create dashboards, etc.

For example, a few of the inbuilt Advanced Excel formulas are given in the image below.

Advanced Excel Formulas - 1

Key Takeaways

  • The Advanced Excel Formulas help us understand the inbuilt advanced formulas used for generating reports for concerns, companies, etc.
  • In Excel, VLOOKUP is the most commonly used LOOKUP function. Another important lookup function is HLOOKUP and XLOOKUP. Therefore, V stands for vertical lookup in VLOOKUP, and H stands for horizontal lookup in HLOOKUP.
  • In VLOOKUP, we cannot have a primary column on the right of the column for which we want to populate the value from another table.
  • One can combine INDEX and MATCH to overcome the limitation of VLOOKUP.
  • We can use the OR function instead of AND to satisfy one of the many conditions.

List Of Top 10 Advanced Excel Formulas & Functions

The Top 10 Advanced Excel Formulas & Functions we will consider in this article are as follows:

  1. VLOOKUP Formula in Excel
  2. INDEX Formula in Excel
  3. MATCH Formula in Excel
  4. IF AND Formula in Excel
  5. IF OR Formula in Excel
  6. SUMIF Formula in Excel
  7. CONCATENATE Formula in Excel
  8. LEFT, MID, and RIGHT Formula in Excel
  9. OFFSET Formula in Excel
  10. TRIM Formula in Excel

#1 – VLOOKUP Formula in Excel

It is one of the most used formulae in Excel mainly due to the simplicity of this formula and its application in looking up a certain value from other tables, which has one standard variable across these tables.

For example, if we have two tables detailing a company’s employee salary and name, with “Employee ID” being a primary column, and we want to get the salary from Table B in Table A.

Advanced Vlookup Example 1

We can use VLOOKUPUse VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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. We cannot have a primary column on the right of the column for which we want to populate the value from another table. The “Employee Salary” column cannot be before the “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 we insert a new column in the database (e.g., insert a new column before “Employee Salary” in Table B), the output of the formula could be different based on the position that we have mentioned in the formula (in the above case, the result would be blank).

#2 – INDEX Formula in Excel

It is used to get the value of a cell in a given table by specifying the number of rows, columns, or both. E.g., to get an employee’s name at the 5th observation. Below is the data.

Advanced INDEX Example 2

We can use the advanced Excel formula below:

Advanced INDEX Example 2-1

We can use the same INDEX formula in getting values along the row. So, for example, when using both row and column numbers, the syntax would look like this:

Advanced INDEX Example 2-2

The above formula would return as “Rajesh Ved.”

Advanced INDEX Example 2-3

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

Advanced INDEX Example 2-4

#3 – MATCH Formula in Excel

It returns the row or column number when a specific string or number is 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 functionMATCH FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more would return 5 as the value.

Advanced Match Example 3-1

The 3rd argument is used for the exact MATCH. We can also use +1 and -1 based on our requirements.

#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 functionExcel IF FunctionIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more to create a new field based on some existing field constraints. But what if we need to consider multiple columns while creating a flag?

E.g., in the below case, we want to flag all the employees whose salary is greater than 50,000. But “Employee ID” is greater than 3.

Advanced IF AND Example 4

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

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 use the OR function in ExcelUse The OR Function In ExcelThe OR function in Excel is used to test various conditions, allowing you to compare two values or statements in Excel. If at least one of the arguments or conditions evaluates to TRUE, it will return TRUE. Similarly, if all of the arguments or conditions are FALSE, it will return FASLE.read more instead of AND if we need to satisfy one of the many conditions.

Advanced IF OR Example 5

If any condition is satisfied in the above cases, we will have the cell populated as 1, else 0. We can substitute 1 or 0 with some substrings with double quotes (“”).

Advanced IF OR Example 5-1

#6 – SUMIF Formula in Excel

The advanced Excel SUMIF function in excelSUMIF Function In ExcelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more

filters the required data based on certain criteria or condition and finds the sum of the returned numeric values.

E.g., We can find the total salaries of the employees whose employee IDs fall under a specific criterion, say greater than three.

Let us enter the SUMIFS formula SUMIFS FormulaSUMIFS is an enhanced version of the SUMIF formula in Excel that enables you to sum any range of data by matching several criteria. read more:

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 an employee ID greater than 3 using COUNTIF instead of SUMIF.

#7 – CONCATENATE Formula in Excel

It is one of the formulas used with multiple variants, which helps us join several text strings into one text string.

For example, if we want to show “Employee ID” and “Employee Name” in a single column.

Advanced Concatenate Example 7

We can use this CONCATENATE formula here.

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. E.g., CONCATENATE(B3,”-“,C3) will result in “1-Aman Gupta”. We can also use this in VLOOKUP when LOOKUP in ExcelLOOKUP In ExcelThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more 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. One could use it based on our requirements. E.g., If we want to remove the first 5 characters from “Employee Name,” we can use the LEFT formula in ExcelLEFT Formula In ExcelThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( "ANAND",2), the result will be AN.read more 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 ExcelRIGHT Formula In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more is also the same. It is just that we would be looking at the character from the right of the string. However, in the case of a MID function in excelMID Function In ExcelThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more, we must give the required text string’s starting position and the string’s length.

#9 – OFFSET Formula in Excel

It is an advanced Excel functions combined with SUM or AVERAGE, which can give a dynamic touch to the calculations. It is best used when we insert continuous rows into an existing database. OFFSET ExcelOFFSET ExcelThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. read more provides a range where we need to mention reference cells, number of rows, and columns.

E.g., If we want to calculate the average of the first 5 employees in the company where we have the salary of employees sorted by employee ID, we can do the following. The calculation below will always give us a salary.

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

It is used to clean up the unimportant spaces in the text. E.g., if we want to remove spaces at the beginning of some names, we can use it by using the TRIM function in Excel TRIM Function In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces.read more as below:

Advanced TRIM Example 1

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

Advanced TRIM Example 10-1

Important Things To Note

  • To avoid the “#NUM!” error, we must ensure to enter argument values or cell references with the right cell values as whether it needs numeric values, or alpha-numeric values.
  • We must check for blank or empty cells before executing the formulas to avoid “#VALUE!” or “#NA” errors.
  • By entering the correct function names, we avoid getting the “#NAME?” errors.

Frequently Asked Questions (FAQs)

1. Where is the VLOOKUP function found in Excel?

The VLOOKUP function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “VLOOKUP” function, as shown below.

Advanced Excel Formulas - FAQ 1

2. Where is the INDEX function found in Excel?

The INDEX function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “INDEX” function, as shown below.

Advanced Excel Formulas - FAQ 2

3. Where is the MATCH function found in Excel?

The MATCH function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “MATCH” function, as shown below.

Advanced Excel Formulas - FAQ 3

4. Where is the OFFSET function found in Excel?

The OFFSET function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “OFFSET” function, as shown below.

Advanced Excel Formulas - FAQ 4

Download Template

This article must help understand the Advanced Excel Formulas with formulas and examples. We can download the template here to use it instantly.

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

This article is a guide to Advanced Excel Formulas. Here, we discuss formulas, VLOOKUP, OFFSET, TRIM, CONCATENATE, INDEX, examples, downloadable excel template. You may learn more about Excel from the following articles: –