If isna match vlookup formula

[maijiuli]

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,

 

[T. Valko]

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=$A2)*(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)

 

[Max]

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)

 

[maijiuli]

Thanks Max!
--
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)

 

[maijiuli]

You guys always amaze me with your brain powers.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Max]

welcome