Multiple lookup function
 

Hello,

I need some help with a multiple lookup function. I think there's a formula
that can be used as an array with either SUMIF or SUMPRODUCT but I can't
remember all the details.

Example:
A1 US
A2 California
A3 100

Formula: I want the formula to say "If A1 is US" and "if A2 = California,"
then 100.

Thanks in advance,
Scott

Multiple lookup function
 

yup. sumproduct would work

=sumproduct(--(country range="US")*--(state range="California")*(last
range)

where country range is like A1:M1 or what have you

make sure your data cells don't have trailing spaces, you could
wildcard (*) your criteria or trim if it does.

this is a good resource:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
View this thread: http://www.excelforum.com/showthread...hreadid=563318

Multiple lookup function
 

Scott,
If your data continues accross (A1:Z1=Country, A2:Z2=State,
A3:Z3=Value) then you could use,

=SUMPRODUCT((A1:Z1="US")*(A2:Z2="California")*(A3: Z3))

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=563318