EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Formula and Functions Complex Formulas in Excel
 

Complex Formulas in Excel

Arun Gupta
Article byArun Gupta
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 9, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Complex Formulas Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Complex Formulas Excel Template

EDUCBA

डाउनलोड Complex Formulas Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW