Nested and Combined Excel Functions Practice Questions with Solutions

Introduction

Excel becomes much more powerful when two or more functions are combined in a single formula. A nested formula means using one function inside another function. In this chapter, you will practice combinations such as IF with AND, IFERROR with lookup functions, INDEX with MATCH, ROUND with calculations, TEXT with dates, and other practical combinations. These questions are designed to help you check your Excel skills using real-world-style data. Nested and Combined Excel Functions Practice questions with solutions to help you understand the concepts.

Question 1: Calculate Final Salary Using IF and ROUND

Problem Statement

Calculate an employee’s final salary after adding a performance bonus.

If the employee’s performance rating is "Excellent", give a 15% bonus. Otherwise, give a 5% bonus. Round the final salary to the nearest whole number.

Excel Data

Excel Solution

In D2, enter:

=ROUND(B2*(1+IF(C2="Excellent",15%,5%)),0)

Copy the formula down.

Expected Output

Concepts Covered

• IF
• ROUND
• Percentage calculation
• Nested functions

Question 2: Calculate a Student’s Result Using IF, AND and AVERAGE

Problem Statement

A student passes only when:

• Maths marks are at least 40.
• Science marks are at least 40.
• English marks are at least 40.
• Overall average is at least 50.

Display "Pass" or "Fail".

Excel Data

Excel Solution

In E2, enter:

=IF(AND(B2>=40,C2>=40,D2>=40,AVERAGE(B2:D2)>=50),"Pass","Fail")

Copy the formula down.

Expected Output

Concepts Covered

• IF
• AND
• AVERAGE
• Multiple conditions

Question 3: Calculate Discount Using IF and AND

Problem Statement

A store provides discounts based on purchase amount and customer type.

Rules:

• Premium customers spending ₹50,000 or more get 15%.
• Premium customers spending less than ₹50,000 get 10%.
• Regular customers spending ₹50,000 or more get 8%.
• All other customers get 5%.

Excel Data

Excel Solution

In D2, enter:

=IF(AND(B2="Premium",C2>=50000),15%,IF(B2="Premium",10%,IF(C2>=50000,8%,5%)))

Copy down.

Expected Output

Question 4: Use IFERROR with INDEX-MATCH

Problem Statement

Find the department of each employee using Employee ID. If the Employee ID does not exist, display "Not Found".

Excel Data

Search Data

Excel Solution

In B9, enter:

=IFERROR(INDEX($C$2:$C$6,MATCH(A9,$A$2:$A$6,0)),"Not Found")

Copy down.

Expected Output

Concepts Covered

• IFERROR
• INDEX
• MATCH
• Nested/combined functions

Question 5: Calculate Net Salary Using IF, SUM and ROUND

Problem Statement

Calculate an employee’s net salary.

Net Salary:

Basic Salary + Allowance − Deduction

If the employee’s attendance is below 90%, reduce the allowance by 20%.

Round the final result to the nearest whole number.

Excel Data

Excel Solution

In F2, enter:

=ROUND(B2+IF(E2<90%,C2*80%,C2)-D2,0)

Copy down.

Expected Output

Question 6: Find Product Price and Calculate Total Using XLOOKUP

Problem Statement

Use XLOOKUP to find the product price and then calculate the total amount based on quantity.

If the product does not exist, display "Product Not Found".

Product Data

Order Data

Excel Solution

In C9, enter:

=IFERROR(XLOOKUP(A9,$A$2:$A$6,$C$2:$C$6),"Not Found")

In D9, enter:

=IF(ISNUMBER(C9),C9*B9,"Not Available")

Copy both formulas down.

Expected Output

Concepts Covered

• XLOOKUP
• IFERROR
• IF
• ISNUMBER
• Multiplication
• Lookup + calculation

Question 7: Create an Employee Performance Category

Problem Statement

Classify employees based on their sales and customer rating.

Rules:

• Sales ≥ ₹100,000 and rating ≥ 4.5 → "Excellent"
• Sales ≥ ₹75,000 and rating ≥ 4.0 → "Good"
• Sales ≥ ₹50,000 → "Average"
• Otherwise → "Needs Improvement"

Excel Data

Excel Solution

In D2, enter:

=IF(AND(B2>=100000,C2>=4.5),"Excellent",IF(AND(B2>=75000,C2>=4),"Good",IF(B2>=50000,"Average","Needs Improvement")))

Copy down.

Expected Output

Question 8: Create a Formatted Employee Joining Date

Problem Statement

Use the Employee ID to find the joining date and display it in a readable format.

Employee Data

Search Data

Excel Solution

In B9, enter:

=IFERROR(TEXT(XLOOKUP(A9,$A$2:$A$6,$C$2:$C$6),"dd-mmm-yyyy"),"Date Not Found")

Copy down.

Expected Output

Concepts Covered

• XLOOKUP
• IFERROR
• TEXT
• Date formatting
• Combined functions

Question 9: Calculate Age Category Using DATEDIF and IF

Problem Statement

Calculate an employee’s age and then categorize them:

• Under 25 → "Young"
• 25 to 40 → "Adult"
• Above 40 → "Senior"

Excel Data

Excel Solution

In C2, enter:

=DATEDIF(B2,TODAY(),"Y")

In D2, enter:

=IF(C2<25,"Young",IF(C2<=40,"Adult","Senior"))

Copy both formulas down.

Expected Output

The exact ages will change automatically depending on the current date.

The category will be based on the calculated age.

Concepts Covered

• DATEDIF
• TODAY
• IF
• Nested logical conditions
• Dynamic date calculation

Question 10: Build a Combined Sales Report Formula

Problem Statement

Create a sales report that calculates the final payable amount.

Rules:

1. Find the product price using XLOOKUP.
2. Multiply price by quantity.
3. If quantity is 10 or more, apply a 10% discount.
4. If quantity is less than 10, apply a 5% discount.
5. Round the final amount.
6. If the Product ID does not exist, display "Invalid Product".

Product Data

Sales Data

Excel Solution

In C9, enter:

=IFERROR(ROUND(XLOOKUP(A9,$A$2:$A$6,$C$2:$C$6)*B9*(1-IF(B9>=10,10%,5%)),0),"Invalid Product")

Copy the formula down.

Expected Output

If the cell is formatted as currency or number with zero decimal places, the displayed result may be rounded according to the cell formatting.

Formula Breakdown

The formula combines several Excel functions:

=IFERROR(
    ROUND(
        XLOOKUP(A9,$A$2:$A$6,$C$2:$C$6)
        *B9
        *(1-IF(B9>=10,10%,5%)),
    0),
"Invalid Product")

It uses:

• XLOOKUP to find the product price.
• IF to determine the discount.
• Multiplication to calculate the order amount.
• ROUND to round the final amount.
• IFERROR to handle an invalid Product ID.

Key Takeaways

• Nested functions mean placing one Excel function inside another.
• Combined functions allow Excel to perform multiple operations in one formula.
• IF can be combined with AND, OR, AVERAGE, ROUND, and lookup functions.
• IFERROR is useful when a lookup or calculation can produce an error.
• XLOOKUP can be combined with calculations to create practical reports.
• TEXT can format dates and numbers inside formulas.
• DATEDIF and TODAY can be combined for dynamic age calculations.
• INDEX and MATCH can be combined with IFERROR for reliable lookups.
• ROUND is useful when calculations produce decimal values.
• Complex formulas should still be readable and logically organized.
• Breaking a complicated formula into smaller parts can make troubleshooting easier.

FAQs

1. What is a nested function in Excel?

A nested function is a function placed inside another function.

For example:

=IF(AVERAGE(B2:D2)>=50,"Pass","Fail")

Here, AVERAGE is inside IF.

2. What is a combined Excel formula?

A combined formula uses multiple Excel functions together to complete a calculation.

For example:

=IFERROR(XLOOKUP(A2,B2:B10,C2:C10),"Not Found")

This combines IFERROR and XLOOKUP.

3. Why should I use nested functions?

Nested functions allow you to perform multiple logical or mathematical operations in one formula.

They are useful for:

• Reports
• Dashboards
• Salary calculations
• Grading systems
• Sales analysis
• Data validation
• Business calculations

4. Can IF be combined with AND?

Yes.

=IF(AND(B2>=50,C2>=50),"Pass","Fail")

This checks whether both conditions are TRUE.

5. Can IF be combined with OR?

Yes.

=IF(OR(B2="Yes",C2="Yes"),"Eligible","Not Eligible")

The result is "Eligible" when at least one condition is TRUE.

6. Can XLOOKUP be combined with IFERROR?

Yes.

=IFERROR(XLOOKUP(A2,B2:B10,C2:C10),"Not Found")

This prevents an error from being displayed when the lookup value does not exist.

7. Can I combine lookup functions with mathematical calculations?

Yes. For example:

=XLOOKUP(A2,B2:B10,C2:C10)*D2

This finds a price and multiplies it by quantity.

8. How do I make a complicated Excel formula easier to understand?

Break the logic into smaller parts and test each section separately. You can also use helper columns when a single formula becomes difficult to read or maintain.

9. Can nested formulas make Excel slower?

Very large workbooks containing thousands of complex formulas can become slower, especially when formulas repeatedly process large ranges. Using efficient ranges and appropriate functions can help performance.

10. Should beginners learn nested Excel functions?

Yes. Once basic functions are understood, nested functions are an important step toward advanced Excel because many practical business formulas require more than one operation.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar