EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Formula and Functions Complex 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

Complex Formulas in Excel

By Arun GuptaArun Gupta

Complex Formulas in Excel

Complex Formulas in Excel (Table of Contents)

  • Introduction to Complex Formulas in Excel
  • Choose Function Serve as Lookup Function
  • VLOOKUP With CHOOSE
  • IF With AND & OR Function

Introduction to Complex Formulas in Excel

Excel comes with many useful formulas; we have a category of Financial, Logical, Text, Date & Time, Lookup & References, and Math & Trig”. Each individual formulas are good enough for intermediate users where they face straight forward situations, but as you progress to the next level, your skill needs to take the elevation, so in that regard, applying more complex formulas in excel plays a vital role in solving complex situations. Keeping this in mind, this article, we will take you through some of the important and much-needed complex formulas. Read on.

Start Your Free Excel Course

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

Choose Function Serve as Lookup Function

Not many of you have used CHOOSE function, but this is very useful to serve as the lookup function as well.

You can download this Complex Formulas Excel Template here – Complex Formulas Excel Template
For example, look at the below data.

Complex Formulas in Excel 1.1

We have data of customers on a monthly basis under this table; we have each customer table. In this table, in cell B13, I have created the drop-down list of the month so that when this drop-down changes, we should get the total of the selected month.

Create one table like the one below.

Complex Formulas in Excel 1.2

Apply VLOOKUP to cell D13.

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,368 ratings)

Complex Formulas in Excel 1.3

Now open CHOOSE function in cell B14.

Complex Formulas in Excel 1.4

Select the INDEX Number as a VLOOKUP cell and make it an absolute reference.

Complex Formulas in Excel 1.5

Now select for each argument each month sales numbers.

Complex Formulas in Excel 1.6

Close the bracket and hit the enter key.

Complex Formulas in Excel 1.7

Now copy and paste the formula to the below cells as well.

Complex Formulas in Excel 1.8

Now CHOOSE formula will show the numbers of the selected month.

Complex Formulas in Excel 1.9

VLOOKUP With CHOOSE Function

We have seen tradition VLOOKUP, but we can combine it with CHOOSE function as well. For example, look at the below data.

VLOOKUP CHOOSE function 1.1

In cells A1 to B11, we have a sales table, and in cell D2, I have created the drop-down list of months, and in cell E2, we need to apply VLOOKUP and get the selected month sales value. But the problem to apply VLOOKUP is a data structure, where the lookup value is to the right of the result column, so VLOOKUP cannot fetch the data from right to left; in this case, we can use CHOOSE function.

VLOOKUP CHOOSE function 1.2

In order to select the table array, we need to open the CHOOSE function here.

VLOOKUP CHOOSE function 1.3

In the INDEX Number argument in flower, brackets enter 1 & 2. Now select the first range as the Month column.

VLOOKUP CHOOSE function 1.4

Select the Sales column as the second range.

VLOOKUP CHOOSE function 1.5

Mention column index number as 2 and range lookup as 0.

VLOOKUP CHOOSE function 1.6

So we got the result for the month of Apr from the table. So, whenever you don’t have an accurate data structure, we can select, CHOOSE function.

IF With AND & OR Function

Logical functions are full of logic to calculate the stuff. Take a look at the below example data.

IF With AND & OR Function 1.1

From the above table, we need to calculate the bonus based on the below conditions.

  • If the Service is more than 1.5 years and if the department is either Sales or Support, then Bonus is 1000.
  • If anything else, then Bonus is 3000.

To calculate the bonus, we need to apply IF, AND, & OR together. Open IF condition in cell D2.

IF With AND & OR Function 1.2

We need to test multiple conditions, open AND function.

IF With AND & OR Function 1.3

Now the next logical is either Sale or Support so, for this open OR condition.

IF With AND & OR Function 1.4

Test whether the title is Sales or Support.

IF With AND & OR Function 1.5

So, if all the supplied conditions are TRUE, then the bonus should be 10000.

IF With AND & OR Function 1.6

If the supplied logical tests are FALSE, then the bonus is 3000.

IF With AND & OR Function 1.7

Close the bracket and hit Enter key; we should get the result.

IF With AND & OR Function 1.8

Copy and paste the formula to the below cells.

IF With AND & OR Function 1.9

So, according to the condition, we got the result. If the service is >1.5 years and the title is either Sales or Support, we will get 10000 or else we will get 3000.

IF With AND & OR Function 1.10

Complex Formula with Wildcard Characters

VLOOKUP requires an exact lookup value to be matched to fetch the data. This is the traditional slogan, but we can still fetch the data by using the particle lookup value. For example, if the lookup value is “VIVO” and in the main table, we can still match using wildcard characters if it is “VIVO Mobile”. We will see one of the examples now; below is the example data.

Wildcard Characters 1.1

We have Lookup Table in column A. In column C, we have lookup values; these lookup values are not exactly the same as the lookup table values. So we will see how to apply VLOOKUP using wild cards.

First, open the VLOOKUP function in cell D1.

Wildcard Characters 1.2

The first argument is the Lookup Value. One of the problems with the lookup value here is we don’t have an exact match, so we need to enclose this lookup value with an asterisk before and after the lookup value.

Wildcard Characters 1.3

Here we have applied two asterisk characters, “*”&C2&”*”. Here asterisk indicates anything in between the wildcard should be matched and return the related result.

Wildcard Characters 1.4

Even though we had just “Infosys”, the asterisk character matched the value in the lookup table and returned the exact result as “Infosys Ltd”.

Similarly, in cell D6, we got an error value as #VALUE! Because there is no word “Mintra” in the lookup table.

Recommended Articles

This is a guide to Complex Formulas in Excel. Here we discuss some of the important and much-needed complex formulas with examples and excel template. You can also go through our other suggested articles to learn more –

  1. SEARCH Formula in Excel
  2. Advanced Formulas in Excel
  3. TRIM Formula in Excel
  4. Text Formula In Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ 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, formulas, 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 Complex Formulas Excel Template

EDUCBA

Download Complex Formulas Excel Template

EDUCBA

डाउनलोड Complex 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