Lookup function for two variables and return another cell?
 

Hi! I am looking for some help. Seems pretty simple but I can't seem to
figure it out. I know I'll need a lookup function and an "AND" function of
some sort... Take a look and let me know what you think.

row/column A B C
D
1 New SSN 1st signed Blue 2
2 New SSN Calc - not signed Green 1
3 Previous SSN 1st signed Red 3


So I need help finding the combination of "New SSN" and "1st signed" and I
need it to return the corresponding number. So for example, this one would
return the number "2". Any suggestions?

Basically I need a formula that looks up "New SSN" in column A and "1st
signed" in column B and then return the corresponding value in column D.
Thanks!

Lookup function for two variables and return another cell?
 

Hi,

Try this ARRAY formula. The 2 lookup values are in E1 & F1. See below on how
to enter and array formula.

=INDEX(D1:D3,MATCH(1,(A1:A3=E1)*(B1:B3=F1),0))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"dcw0405" wrote:

Hi! I am looking for some help. Seems pretty simple but I can't seem to
figure it out. I know I'll need a lookup function and an "AND" function of
some sort... Take a look and let me know what you think.

row/column A B C
D
1 New SSN 1st signed Blue 2
2 New SSN Calc - not signed Green 1
3 Previous SSN 1st signed Red 3


So I need help finding the combination of "New SSN" and "1st signed" and I
need it to return the corresponding number. So for example, this one would
return the number "2". Any suggestions?

Basically I need a formula that looks up "New SSN" in column A and "1st
signed" in column B and then return the corresponding value in column D.
Thanks!

Lookup function for two variables and return another cell?
 

Assuming that the combination of New SSN and 1st signed is unique...

If you're using Excel 2007:

Use cells to hold the criteria...

F1 = New SSN
G1 = 1st signed

Then:

=SUMIFS(D1:D10,A1:A10,F1,B1:B10,G1)

This one will work in any version of Excel:

=SUMPRODUCT(--(A1:A10=F1),--(B1:B10=G1),D1:D10)

--
Biff
Microsoft Excel MVP


"dcw0405" wrote in message
...
Hi! I am looking for some help. Seems pretty simple but I can't seem to
figure it out. I know I'll need a lookup function and an "AND" function of
some sort... Take a look and let me know what you think.

row/column A B C
D
1 New SSN 1st signed Blue
2
2 New SSN Calc - not signed Green 1
3 Previous SSN 1st signed Red
3


So I need help finding the combination of "New SSN" and "1st signed" and I
need it to return the corresponding number. So for example, this one would
return the number "2". Any suggestions?

Basically I need a formula that looks up "New SSN" in column A and "1st
signed" in column B and then return the corresponding value in column D.
Thanks!