Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function with Multiple Conditions | Excel Discussion (Misc queries) | |||
Lookup function gives wrong values occasionally | Excel Discussion (Misc queries) | |||
Help with a lookup function that will give me multiple answers | Excel Discussion (Misc queries) | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
lookup function 1 | Excel Worksheet Functions |