Lookup with two variables

[malvis]

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

 

[NBVC]

If the Matches to A1 & B1 combined is unique in Sheet 2, then you can
use Sumproduct...

e.g.


Code:

 

[Eduardo]

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

 

[Jim Thomlinson]

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...

 

[Bernard Liengme]

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