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

Hello,

I am working on an Excel project in which I need to return one cell
value from an array based on multiple conditions. Let me explain my
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 where
for example Year=2005 & Product=C100 & Location=Atlanta. For each
combination like this there is only one possible result to return,
however there are multiple records with year = 2005, Product = C100 and
so on.

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

Thanks so much for your help!


--
rmcnam05
------------------------------------------------------------------------
rmcnam05's Profile: http://www.excelforum.com/member.php...o&userid=27978
View this thread: http://www.excelforum.com/showthread...hreadid=474861

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

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

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

--
HTH

Bob Phillips

"rmcnam05" wrote in
message ...

Hello,

I am working on an Excel project in which I need to return one cell
value from an array based on multiple conditions. Let me explain my
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 where
for example Year=2005 & Product=C100 & Location=Atlanta. For each
combination like this there is only one possible result to return,
however there are multiple records with year = 2005, Product = C100 and
so on.

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

Thanks so much for your help!


--
rmcnam05
------------------------------------------------------------------------
rmcnam05's Profile:
http://www.excelforum.com/member.php...o&userid=27978
View this thread: http://www.excelforum.com/showthread...hreadid=474861

Hi,

You may also use the following array formula

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

Regards,

Ashish Mathur

"rmcnam05" wrote:


Hello,

I am working on an Excel project in which I need to return one cell
value from an array based on multiple conditions. Let me explain my
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 where
for example Year=2005 & Product=C100 & Location=Atlanta. For each
combination like this there is only one possible result to return,
however there are multiple records with year = 2005, Product = C100 and
so on.

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

Thanks so much for your help!


--
rmcnam05
------------------------------------------------------------------------
rmcnam05's Profile: http://www.excelforum.com/member.php...o&userid=27978
View this thread: http://www.excelforum.com/showthread...hreadid=474861