Excel Howtos, Learn Excel

How to Look up Based on Multiple Conditions

Last updated on November 2, 2010

Chandoo

This article is part of our VLOOKUP Week. Read more.

Situation

Not always we want to lookup values based on one search parameter. For eg. Imagine you have data like below and you want to find how much sales Joseph made in January 2007 in North region for product “Fast car”?

Data:

Solution

Simple, use your index finger to scan the list and find the match 😉

Of course, that wouldn’t be scalable. Plus, you may want to put your index finger to better use, like typing . So, lets come up with some formulas that do this for us.

You can extract items from a table that match multiple criteria in multiple ways. See the examples to understand the techniques:

Using SUMIFS Formula [help]
Formula	`=SUMIFS(lstSales, lstSalesman,valSalesman, lstMonths,valMonth, lstRegion,valRegion, lstProduct,valProduct)`
Result	1592

Using SUMPRODUCT Formula [help]
Formula	`=SUMPRODUCT(lstSales,(lstSalesman=valSalesman)(lstMonths=valMonth)(lstRegion=valRegion)* (lstProduct=valProduct))`
Result	1592

Using INDEX & Match Formulas (Array Formula) [help]
Formula	`{=INDEX(lstSales,MATCH(valSalesman&valMonth&valRegion&valProduct, lstSalesman&lstMonths&lstRegion&lstProduct,0))}`
Result	1592

Using VLOOKUP Formula [help]
Formula	`=VLOOKUP(valMonth&valSalesman&valRegion&valProduct,tblData2,7,FALSE)`
Result	1592
Conditions:	A helper column that concatenates month, salesman, region & product in the left most column of tblData2

Using SUM (Array Formula) [help]
Formula	`{=SUM(lstSales(lstSalesman=valSalesman)(lstMonths=valMonth)* (lstRegion=valRegion)*(lstProduct=valProduct))}`
Result	1592