Return a cell value based on specific combinations of cells in an array

[rmcnam05]

Hello,

I am working on an Excel project in which I need to return one cel
value from an array based on multiple conditions. Let me explain m
example as best I can.

I have an array setup with multiple fields such as:
Year , Product, Location, Revenue

What I need to do is return the cell value in the revenue cell wher
for example Year=2005 & Product=C100 & Location=Atlanta. For eac
combination like this there is only one possible result to return
however there are multiple records with year = 2005, Product = C100 an
so on.

Is there a formula or combination of formulas I can use to return m
result?

Thanks so much for your help

 

[Bob Phillips]

=sumproduct(--(A2:A20=2005),--(B2:B20=C100),--(C2:C20="Atlanta"),D220)

adjust the ranges to suit, or better if you named them

=sumproduct(--(year=2005),--(product=C100),--(location="Atlanta"),revenue)

 

[Guest]

Hi,

You may also use the following array formula

=sum(if((range1="2005")*(range2="product")*(range3="location"),revenue range))

Regards,

Ashish Mathur