Lookup with two variables

  • Thread starter Thread starter malvis
  • Start date Start date
M

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