Gandalf's notebook: How to Look up Based on Multiple Conditions

From chandoo.org

How to Look up Based on Multiple Conditions:

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:

Data for this Example -Looing up Based on More than One Value

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