Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700 rows).

example:

A B C D
E
SSN Employee First Name Last Name Dependent Fname DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate and
address (aprx 3400 rows).

example:

A B D E F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123 A St
111111111 Tom Sarah Brady 2/1/2003 321 B St
111111111 Tom Joel Brady 3/1/2005 321
B St

I would like to put the dependents DOB and address from the 2nd worksheet to
the 1st ws accurately. So far I've ever only used a match formula where you
match 1 unique key (SSN) but have never used a formula where you first match
one thing and if its true than you match another thing and if that's true
then you return a specified array which in this case would be columns F and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

Assume data on sheet 2 is in the range A2:G4

Using the SSN and Dep Fname as the keys.

Enter this array formula** in the DepDob column and copy across to the Dep
Address column:

=INDEX(Sheet2!E$2:E$4,MATCH(1,(Sheet2!$A$2:$A$4=$A 2)*(Sheet2!$C$2:$C$4=$D2),0))

Now, select both formula cells then copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"maijiuli" wrote in message
...
Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700
rows).

example:

A B C D
E
SSN Employee First Name Last Name Dependent Fname DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate and
address (aprx 3400 rows).

example:

A B D E
F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123 A
St
111111111 Tom Sarah Brady 2/1/2003 321
B St
111111111 Tom Joel Brady 3/1/2005
321
B St

I would like to put the dependents DOB and address from the 2nd worksheet
to
the 1st ws accurately. So far I've ever only used a match formula where
you
match 1 unique key (SSN) but have never used a formula where you first
match
one thing and if its true than you match another thing and if that's true
then you return a specified array which in this case would be columns F
and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

Presuming the SSN and the Dependent Fname together suffices as the unique
match keys

In Sheet1,

Put in F2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,(Sheet2!$A$2:$A$3500=$A2)*(Sheet2 !$C$2:$C$3500=$D2),0)),"",INDEX(Sheet2!F$2:F$3500, MATCH(1,(Sheet2!$A$2:$A$3500=$A2)*(Sheet2!$C$2:$C$ 3500=$D2),0)))
Copy F2 across to G2, fill down as far as required

Col F will return the DOBs, col G returns the addresses from Sheet2 (that's
where you have ~3500 rows)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"maijiuli" wrote:
Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700 rows).

example:

A B C D
E
SSN Employee First Name Last Name Dependent Fname DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate and
address (aprx 3400 rows).

example:

A B D E F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123 A St
111111111 Tom Sarah Brady 2/1/2003 321 B St
111111111 Tom Joel Brady 3/1/2005 321
B St

I would like to put the dependents DOB and address from the 2nd worksheet to
the 1st ws accurately. So far I've ever only used a match formula where you
match 1 unique key (SSN) but have never used a formula where you first match
one thing and if its true than you match another thing and if that's true
then you return a specified array which in this case would be columns F and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

Thanks Max!
--
Thank You!


"Max" wrote:

Presuming the SSN and the Dependent Fname together suffices as the unique
match keys

In Sheet1,

Put in F2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,(Sheet2!$A$2:$A$3500=$A2)*(Sheet2 !$C$2:$C$3500=$D2),0)),"",INDEX(Sheet2!F$2:F$3500, MATCH(1,(Sheet2!$A$2:$A$3500=$A2)*(Sheet2!$C$2:$C$ 3500=$D2),0)))
Copy F2 across to G2, fill down as far as required

Col F will return the DOBs, col G returns the addresses from Sheet2 (that's
where you have ~3500 rows)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"maijiuli" wrote:
Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700 rows).

example:

A B C D
E
SSN Employee First Name Last Name Dependent Fname DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate and
address (aprx 3400 rows).

example:

A B D E F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123 A St
111111111 Tom Sarah Brady 2/1/2003 321 B St
111111111 Tom Joel Brady 3/1/2005 321
B St

I would like to put the dependents DOB and address from the 2nd worksheet to
the 1st ws accurately. So far I've ever only used a match formula where you
match 1 unique key (SSN) but have never used a formula where you first match
one thing and if its true than you match another thing and if that's true
then you return a specified array which in this case would be columns F and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

You guys always amaze me with your brain powers.
--
Thank You!


"T. Valko" wrote:

Assume data on sheet 2 is in the range A2:G4

Using the SSN and Dep Fname as the keys.

Enter this array formula** in the DepDob column and copy across to the Dep
Address column:

=INDEX(Sheet2!E$2:E$4,MATCH(1,(Sheet2!$A$2:$A$4=$A 2)*(Sheet2!$C$2:$C$4=$D2),0))

Now, select both formula cells then copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"maijiuli" wrote in message
...
Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700
rows).

example:

A B C D
E
SSN Employee First Name Last Name Dependent Fname DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate and
address (aprx 3400 rows).

example:

A B D E
F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123 A
St
111111111 Tom Sarah Brady 2/1/2003 321
B St
111111111 Tom Joel Brady 3/1/2005
321
B St

I would like to put the dependents DOB and address from the 2nd worksheet
to
the 1st ws accurately. So far I've ever only used a match formula where
you
match 1 unique key (SSN) but have never used a formula where you first
match
one thing and if its true than you match another thing and if that's true
then you return a specified array which in this case would be columns F
and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"maijiuli" wrote in message
...
You guys always amaze me with your brain powers.
--
Thank You!


"T. Valko" wrote:

Assume data on sheet 2 is in the range A2:G4

Using the SSN and Dep Fname as the keys.

Enter this array formula** in the DepDob column and copy across to the
Dep
Address column:

=INDEX(Sheet2!E$2:E$4,MATCH(1,(Sheet2!$A$2:$A$4=$A 2)*(Sheet2!$C$2:$C$4=$D2),0))

Now, select both formula cells then copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"maijiuli" wrote in message
...
Hello,

I have a question about a formula which I am stuck on.

I was given 2 worksheets:

First with employee SSN, name and their dependent's names (aprxmate 700
rows).

example:

A B C
D
E
SSN Employee First Name Last Name Dependent Fname
DLName
123456789 Peyton Manning Jordan
Manning
111111111 Tom Brady Sarah
Brady
111111111 Tom Brady Joel
Brady


Second worksheet with same things but also have Dependents Birthdate
and
address (aprx 3400 rows).

example:

A B D E
F
G
SSN Employee First Name DFname DLname DepDOB DepAddress
123456789 Peyton Jordan Manning 1/1/1999 123
A
St
111111111 Tom Sarah Brady 2/1/2003
321
B St
111111111 Tom Joel Brady 3/1/2005
321
B St

I would like to put the dependents DOB and address from the 2nd
worksheet
to
the 1st ws accurately. So far I've ever only used a match formula
where
you
match 1 unique key (SSN) but have never used a formula where you first
match
one thing and if its true than you match another thing and if that's
true
then you return a specified array which in this case would be columns F
and G
from worksheet 2. Please let me know if this is possible.

As always thank you for looking,

--
Thank You!

welcome
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"maijiuli" wrote in message
...
Thanks Max!