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

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

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

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

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