Using Excel Formulas to Pick Values from a Table with Multiple Conditions

Person typing on laptop

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

The Challenge of Conditional Value Selection in Excel Tables

When working with large datasets, it’s common to need specific values based on multiple criteria. For example, you might have a table that lists electrical components and their wattage ratings for different lengths, heights, and types.

The Problem: Selecting Values Based on Multiple Criteria

You want an Excel formula that can pick a value from the table based on three inputs:
1) Length
2) Height
3) Type

Spreadsheet closeup with numbers

Why This Happens: The Complexity of Multi-Condition Lookups

The challenge lies in finding a formula that can handle multiple conditions simultaneously. Standard lookup functions like VLOOKUP or HLOOKUP only work well for single-column lookups, and INDEX/MATCH combinations require careful setup.

Step-by-Step Solution: Using Excel Formulas

The solution involves using the INDEX and MATCH combination to handle multiple criteria. Here’s a step-by-step guide:

Example Table Setup

Length | Height | Type  | Watts
-------|--------|-------|-------
10     |   5    | A     | 20
10     |   6    | B     | 40
...

Step-by-Step Formula Construction

Step 1: Identify the Criteria Ranges and Values.

The range for Length is A2:A10 (adjust as needed)
The range for Height is B2:B10 (adjust as needed)
The range for Type is C2:C10
Watts are in D2:D10

Step 2: Use MATCH to Find the Row Number.

=MATCH(1, (A$2:A$10=E3) * (B$2:B$10=F3) * (C$2:C$10=G3), 0)

Explanation:
– E3 contains the Length criteria
– F3 contains the Height criteria
– G3 contains the Type criteria

Step 3: Use INDEX to Retrieve the Value.

=INDEX(D$2:D$10, MATCH(1, (A$2:A$10=E3) * (B$2:B$10=F3) * (C$2:C$10=G3), 0))

This formula will return the Watts value from column D that matches all three criteria.

Alternative Approach: Using CelTools for Advanced Lookups

For frequent users, CelTools handles this with a single click…

The manual approach works well but can be cumbersome. For those who frequently need to perform multi-criteria lookups, CelTools offers an advanced solution that simplifies the process.

Real-World Example 1: Electrical Components

Length | Height | Type  | Watts
-------|--------|-------|-------
10     |   5    | A     | 20
10     |   6    | B     | 40
...

Using the formula above, you can quickly find that for Length = 10, Height = 5, and Type = “A”, the Watts value is 20.

Real-World Example 2: Product Inventory Management

Product ID | Size   | Color    | Price
-----------|--------|----------|------
P1         | M      | Red      | $5.99
...

The same formula can be adapted to find the price of a product based on its Product ID, size, and color.

Real-World Example 3: Employee Data Analysis

EmployeeID | Department   | Role     | Salary
-----------|--------------|----------|-------
E01        | HR           | Manager  | $75K
...

The formula can also be used to find an employee’s salary based on their Employee ID, department, and role.

Advanced Variation: Handling Multiple Matches with VBA

Rather than building this from scratch…

If there are multiple matches or if you need more advanced functionality (like returning all matching rows), consider using a simple VBA macro:

Sub FindValue()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim lengthCriteria, heightCriteria, typeCriteria As String
    lengthCriteria = ws.Range("E3").Value ' Length criteria cell reference
    heightCriteria = ws.Range("F3").Value  ' Height criteria cell reference
    typeCriteria = ws.Range("G3").Value   ' Type criteria cell reference

    Dim lastRow As Long, i As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

    For i = 2 To lastRow
        If ws.Cells(i, 1) = lengthCriteria And _
           ws.Cells(i, 2) = heightCriteria And _
           ws.Cells(i, 3) = typeCriteria Then
            MsgBox "Watts: " & ws.Cells(i, 4).Value ' Output the Watts value in a message box or write to another cell
        End If
    Next i

End Sub

Common Mistakes and Misconceptions

Advanced users often turn to CelTools because it…

The most common mistake is not using absolute references ($) for the ranges in your formula, which can lead to incorrect row numbers when copied.
Another frequent issue is forgetting that MATCH returns an error if no match is found. You may need to wrap your INDEX/MATCH with IFERROR or similar functions to handle cases where criteria don’t exist.

Conclusion: Combining Manual Techniques and Specialized Tools for Optimal Results

The combination of manual Excel formulas like the INDEX/MATCH approach, along with specialized tools such as CelTools, provides a robust solution for handling complex lookups. While you can manually construct these formulas to suit your needs, leveraging advanced tools can save time and reduce errors.

Technical Summary

The manual method using Excel’s built-in functions is powerful but requires careful setup. For frequent users or those dealing with very large datasets, CelTools offers a streamlined solution that simplifies the process while ensuring accuracy.

Team working with laptops