Lookup with two variables

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
 
N

NBVC

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

e.g.


Code:
 
E

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
 
J

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

Bernard Liengme

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top