Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with two variables
How do I perform a lookup with two variable that are text?
Cells: A1 is "Away" and B1 is "Tan" This is in Sheet 2: Home Red 1 Home Tan 1 Away Red 4 Away Tan 5 So, I want a formula that will return the number in Sheet 2 column C (in this example) only after it looks at A1 and B1, looks in Sheet 2, and then returns 5. I'm thinking this is a lookup but I'm not sure. Thanks, Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with two variables
If the Matches to A1 & B1 combined is unique in Sheet 2, then you can use Sumproduct... e.g. Code: -------------------- =Sumproduct(--(Sheet2!$A$2:$A$100=A1),--(Sheet2!$B$2:$B$100=B1),$C$2:$C$100) -------------------- adjusting ranges and references to suit. -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109370 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with two variables
This will work. Note that it is not actually looking up the value but rather
it is adding up all of the entries that are both Away and Tan. =SUMPRODUCT(--(A1=Sheet2!$A$2:$A$5), --(B1=Sheet2!$B$2:$B$5), Sheet2!$C$2:$C$5) If this works for you but you need more info just ask... -- HTH... Jim Thomlinson "malvis" wrote: How do I perform a lookup with two variable that are text? Cells: A1 is "Away" and B1 is "Tan" This is in Sheet 2: Home Red 1 Home Tan 1 Away Red 4 Away Tan 5 So, I want a formula that will return the number in Sheet 2 column C (in this example) only after it looks at A1 and B1, looks in Sheet 2, and then returns 5. I'm thinking this is a lookup but I'm not sure. Thanks, Matt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with two variables
HI,
I assume you want the result in sheet 1 in Cell A1 =sumproduct(--(sheet2!$A$1:$A$100="Away"),--(sheet2!$B$1:$B$100="Tan"),Sheet2!$C$1:$C$100) Change the range to fit your needs but remember the range has to be the same in the three parts of the formula, if you are using excel 2007 use =sumproduct(--(sheet2!A:A="Away"),--(sheet2!B:B="Tan"),Sheet2!C:C) if this helps please click yes, thanks "malvis" wrote: How do I perform a lookup with two variable that are text? Cells: A1 is "Away" and B1 is "Tan" This is in Sheet 2: Home Red 1 Home Tan 1 Away Red 4 Away Tan 5 So, I want a formula that will return the number in Sheet 2 column C (in this example) only after it looks at A1 and B1, looks in Sheet 2, and then returns 5. I'm thinking this is a lookup but I'm not sure. Thanks, Matt |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup with two variables
In all versions of Excel:
=SUMPRODUCT(--(Sheet2!A1A:10="Away"), --(Sheet2!B1:B10="Tan"), C1:C10) In Excel 2007 only you can use full column references =SUMPRODUCT(--(Sheet2!A:A="Away"), --(Sheet2!B:B="Tan"), C:C) In Excel 2007 only you can use SUMIFS (note final S) =SUMIFS(C1:C20, A1:A20, "Away", B1:B20, "Tan") For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "malvis" wrote in message ... How do I perform a lookup with two variable that are text? Cells: A1 is "Away" and B1 is "Tan" This is in Sheet 2: Home Red 1 Home Tan 1 Away Red 4 Away Tan 5 So, I want a formula that will return the number in Sheet 2 column C (in this example) only after it looks at A1 and B1, looks in Sheet 2, and then returns 5. I'm thinking this is a lookup but I'm not sure. Thanks, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 LookUp Variables One Result | Excel Worksheet Functions | |||
Lookup with Variables | Excel Worksheet Functions | |||
lookup 2 variables | Excel Discussion (Misc queries) | |||
Lookup (multiple variables) | Excel Worksheet Functions | |||
< |
Excel Worksheet Functions |