Function Problems

How to Perform an Excel Lookup Using Multiple Criteria

Rana Garcia Oct 1, 2026 868 views

Question details

The user wants to retrieve a specific unit price based on three criteria (description, size, and duration) selected from dropdown lists.

How to Look Up Data Using Multiple Criteria in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Retrieving specific pricing data from a master dataset using multiple data validation dropdown lists.
Observed behavior: The user requires an automated or formula-driven method to fetch the correct prices without manually filtering the data 30-50 times.

Before you start

Ensure that your lookup table has consistently formatted columns for Description, Size, Unit, and Price, and that your data-validation dropdown values exactly match the text in the lookup table without extra spaces.

Solution 1Recommended

Use the SUMIFS Function for Numeric Results

SUMIFS is the most efficient and straightforward formula when you need to return a numeric value (like a unit price) based on multiple conditions.

The SUMIFS function adds up values that meet multiple criteria. As long as your master dataset does not contain duplicate rows for the exact same description, size, and unit, SUMIFS will accurately return the single corresponding unit price.

Select the target cell

Click the cell where you want the unit price to be displayed based on the dropdown selections.

Enter the SUMIFS formula

Type =SUMIFS(Price_Column, Description_Column, Dropdown_Desc, Size_Column, Dropdown_Size, Unit_Column, Dropdown_Unit) in the formula bar.

Calculate the result

Press Enter. The cell will now display the unit price that perfectly matches all three dropdown criteria.

Use the SUMIFS Function for Numeric Results

Preventing Data Errors: If multiple rows match all criteria, SUMIFS will sum their prices together. Ensure your lookup table entries are unique.

Solution 2

Use the FILTER Function in Microsoft 365

If you are using Microsoft 365 or Office 2021, the FILTER function can extract exact matching records dynamically, regardless of whether the output is numeric or text.

Solution 3

Automate Bulk Lookups with VBA Macros

For processing 30-50 repetitive lookups at once without manually dragging formulas, a VBA macro can automate the filtering and retrieval process.

Advanced Spreadsheet Tool

Set Up Multi-Criteria Data Lookups Easily with WPS Spreadsheet

WPS Office offers a free, lightweight, and comprehensive Spreadsheet tool that effortlessly handles complex formulas, data validation dropdowns, and VBA macros to streamline your pricing and inventory tasks.

1. Open your dataset: Launch WPS Spreadsheet and open your inventory or pricing workbook.
2. Create data validation lists: Go to the Data tab, click Data Validation, and set your Source ranges to create dropdown lists for Description, Size, and Unit.
3. Apply the lookup formula: Select the target price cell, go to the Formulas tab, and insert the SUMIFS function matching your criteria.
4. Get instant results: Press Enter to dynamically fetch the corresponding unit price based on your dropdown selections.

100% compatible with Microsoft Excel formulas like SUMIFS, FILTER, and VLOOKUP.Easily create multi-level data validation dropdowns for accurate user input.Lightweight design ensures fast calculation of complex multi-criteria datasets.Supports advanced VBA macros for automating repetitive lookups on large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP with multiple criteria?

Standard VLOOKUP only searches for a single value in the first column. To use VLOOKUP with multiple criteria, you must create a 'helper column' in your dataset that concatenates the criteria (e.g., Description&Size&Unit) and search against that combined string.

How do I use INDEX and MATCH for multiple conditions?

You can use an array formula setup like =INDEX(Price_Range, MATCH(1, (Desc_Range=Desc_Cell)*(Size_Range=Size_Cell), 0)). Depending on your software version, you may need to press Ctrl+Shift+Enter to evaluate it correctly.

Why does my SUMIFS formula return a zero instead of the correct price?

This usually happens due to mismatched data. Check for trailing spaces in your dropdown selections or master table. Ensure that the text formatting matches exactly, as SUMIFS requires perfect matches to calculate the sum.

Excel & Data Problems