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

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

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

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

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