Two-Way and Multiple-Criteria Lookup Practice Questions with Solutions

Introduction

Two-way and multiple-criteria lookups are useful when one lookup condition is not enough. For example, you may need to find a product price based on Product + City, employee salary based on Employee + Department, or sales based on Salesperson + Month. In this chapter, you will practice INDEX, MATCH, XLOOKUP, and logical conditions through different Excel problems. Two-Way and Multiple-Criteria Lookup Practice questions with solutions to help you understand the concepts.

Question 1: Find Sales Using Employee and Month

Problem Statement

Find the sales amount using both the salesperson’s name and the month.

Excel Data

Search Data

Excel Solution

In C9, enter:

=INDEX($B$2:$E$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$E$1,0))

Copy the formula down.

Expected Output

Concepts Covered

• Two-way lookup
• INDEX
• Multiple MATCH functions
• Row and column lookup

Question 2: Find Product Price Based on Product and City

Problem Statement

A company sells the same products at different prices in different cities. Find the correct price using both Product and City.

Excel Data

Search Data

Excel Solution

In C9, enter:

=INDEX($B$2:$E$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$E$1,0))

Copy down.

Expected Output

Question 3: Find an Employee’s Salary Using Two Criteria

Problem Statement

An employee database contains employees from different departments. Find the salary using both Employee Name and Department.

Excel Data

Search Data

Excel Solution

Use INDEX with multiple conditions:

=INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=A10)*($B$2:$B$7=B10),0))

In modern Excel, press Enter. Older Excel versions may require Ctrl + Shift + Enter for this type of array formula.

Expected Output

Concepts Covered

• Multiple criteria
• INDEX
• MATCH
• Boolean conditions
• Array-style lookup

Question 4: Find Student Marks Using Student and Subject

Problem Statement

Find a student’s marks using both the student’s name and subject.

Excel Data

Search Data

Excel Solution

In C9, enter:

=INDEX($B$2:$E$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$E$1,0))

Copy down.

Expected Output

Question 5: Multiple-Criteria Lookup Using XLOOKUP

Problem Statement

Find an employee’s salary using both Employee Name and Department with XLOOKUP.

Excel Data

Search Data

Excel Solution

In C9, enter:

=XLOOKUP(1,($A$2:$A$7=A9)*($B$2:$B$7=B9),$C$2:$C$7,"Not Found")

Copy down.

Expected Output

This method allows XLOOKUP to check more than one condition.

Question 6: Find Inventory Using Product, City and Category

Problem Statement

A company has inventory records containing three criteria. Find the stock quantity using:

• Product
• City
• Category

Excel Data

Search Data

Excel Solution

In D11, enter:

=XLOOKUP(1,($A$2:$A$9=A11)*($B$2:$B$9=B11)*($C$2:$C$9=C11),$D$2:$D$9,"Not Found")

Copy down.

Expected Output

Concepts Covered

• Three-criteria lookup
• XLOOKUP
• Multiple Boolean conditions
• Inventory lookup

Question 7: Find the Correct Discount Using Product and Customer Type

Problem Statement

A company gives different discounts depending on the product and customer type.

Excel Data

Search Data

Excel Solution

In C9, enter:

=INDEX($B$2:$D$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$D$1,0))

Copy down.

Expected Output

This is another practical two-way lookup.

Question 8: Find Sales Using Region and Quarter

Problem Statement

Find sales based on both region and quarter.

Excel Data

Search Data

Excel Solution

In C8, enter:

=INDEX($B$2:$E$5,MATCH(A8,$A$2:$A$5,0),MATCH(B8,$B$1:$E$1,0))

Copy down.

Expected Output

Question 9: Multiple-Criteria Lookup with a Calculated Result

Problem Statement

An employee table contains department, monthly sales, and commission rate. Calculate the commission amount for the employee matching both Employee Name and Department.

Excel Data

Search Data

Excel Solution

Use XLOOKUP to find the sales and commission rate based on both criteria.

For example, the commission amount can be calculated directly with:

=XLOOKUP(1,($A$2:$A$7=A10)*($B$2:$B$7=B10),$C$2:$C$7)*XLOOKUP(1,($A$2:$A$7=A10)*($B$2:$B$7=B10),$D$2:$D$7)

Copy down.

Expected Output

This combines multiple-criteria lookup with a calculation.

Question 10: Build a Complete Multi-Criteria Lookup Report

Problem Statement

Create a lookup report for an online store.

The database contains:

• Product
• City
• Customer Type
• Price
• Stock

The user enters Product, City, and Customer Type. Return the applicable price and stock.

Excel Data

Search Data

Excel Solution

For Price, enter:

=XLOOKUP(1,($A$2:$A$11=A14)*($B$2:$B$11=B14)*($C$2:$C$11=C14),$D$2:$D$11,"Not Found")

For Stock, enter:

=XLOOKUP(1,($A$2:$A$11=A14)*($B$2:$B$11=B14)*($C$2:$C$11=C14),$E$2:$E$11,"Not Found")

Copy both formulas down.

Expected Output

Concepts Covered

• Three-criteria lookup
• XLOOKUP
• Multiple Boolean conditions
• Returning different columns
• Error handling
• Practical business lookup report

Key Takeaways

• A two-way lookup uses both a row condition and a column condition.
• INDEX + two MATCH functions is a powerful way to perform two-way lookups.
• Multiple-criteria lookups use more than one condition to identify the correct record.
• XLOOKUP can handle multiple criteria by combining Boolean conditions.
• Multiplying conditions such as:

(condition1)*(condition2)

allows Excel to identify rows where both conditions are TRUE.

• Three or more criteria can be combined in the same lookup formula.
• IFERROR or the XLOOKUP not-found argument can handle missing records.
• Multi-criteria lookups are useful for inventory, sales, employee, pricing, student, and customer databases.
• Two-way lookups are particularly useful when data is arranged horizontally and vertically.
• Understanding multiple-criteria lookup formulas prepares you for advanced Excel reporting and dashboards.

FAQs

1. What is a two-way lookup in Excel?

A two-way lookup finds a value using both a row and a column condition.

For example:

Salesperson + Month → Sales

The salesperson identifies the row and the month identifies the column.

2. What is a multiple-criteria lookup?

A multiple-criteria lookup uses two or more conditions to identify a record.

For example:

Employee + Department → Salary

Both conditions must match the same record.

3. How does INDEX-MATCH perform a two-way lookup?

The first MATCH identifies the row and the second MATCH identifies the column.

=INDEX(B2:E10,MATCH(A2,A2:A10,0),MATCH(B2,B1:E1,0))

INDEX then returns the value at their intersection.

4. How can XLOOKUP handle multiple criteria?

Multiple conditions can be multiplied together:

=XLOOKUP(1,(A2:A10=G2)*(B2:B10=H2),C2:C10)

The formula searches for the row where both conditions are TRUE.

5. Can I use three criteria with XLOOKUP?

Yes. For example:

=XLOOKUP(1,(A2:A10=G2)*(B2:B10=H2)*(C2:C10=I2),D2:D10)

This checks three conditions.

6. What does the * mean in a multiple-criteria lookup?

In this type of formula, * works like an AND operation.

If both conditions are TRUE, the result is 1.

If either condition is FALSE, the result becomes 0.

7. Can INDEX-MATCH handle three criteria?

Yes. Multiple conditions can be combined inside MATCH.

For example:

=INDEX(D2:D10,MATCH(1,(A2:A10=G2)*(B2:B10=H2)*(C2:C10=I2),0))

8. What happens when multiple records match the criteria?

Standard XLOOKUP returns the first matching record. If your data can legitimately contain multiple matching records and you need all of them, functions such as FILTER are more appropriate.

9. Where are multiple-criteria lookups used in real Excel work?

They are commonly used in:

• Employee databases
• Inventory systems
• Sales reports
• Product pricing
• Customer records
• Student databases
• Financial reports
• Business dashboards

10. Should I learn two-way lookup before multiple-criteria lookup?

Yes. Understanding a two-way lookup first makes multiple-criteria formulas easier to understand because both concepts involve using more than one condition to identify the required result.

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

Rishabh Kumar