The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.

Assuming:
A1:A3 = First Names
B1:B3 = Last Names
C1:C3 = SS Numbers

F1 = First Name
G1 = Last Name

If the SS#'s are formatted as numbers you can use:
=SUMPRODUCT(--(A1:A3=F1),--(B1:B3=G1),C1:C3)

If formatted as text use an array formula (entered w/Control+Shift+Enter)
=INDEX(C1:C3,MATCH(F1&G1,A1:A3&B1:B3))

Change ranges as necessary.

"CraigS" wrote:

The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.

My SS# are TEXT. I tried itin the databases and got #VALUE as answer.

I then setup two new excel worksheets (i.e. Book1 and Book2) and set up your
little example using the Index formula and still got #VALUE. I also went
ahead and specifically formatted the SS# as TEXT so there was no issue there.
So i'm not sure exactly what is wrong.

"JMB" wrote:

Assuming:
A1:A3 = First Names
B1:B3 = Last Names
C1:C3 = SS Numbers

F1 = First Name
G1 = Last Name

If the SS#'s are formatted as numbers you can use:
=SUMPRODUCT(--(A1:A3=F1),--(B1:B3=G1),C1:C3)

If formatted as text use an array formula (entered w/Control+Shift+Enter)
=INDEX(C1:C3,MATCH(F1&G1,A1:A3&B1:B3))

Change ranges as necessary.

"CraigS" wrote:

The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.

One thing that can cause that is by simply hitting Enter after typing in the
formula (which doesn't work for array formulas). As mentioned, you must hold
down the Control and Shift keys, then hit Enter. If done properly, Excel
will put braces { } around the formula.

Are you still getting an error? To clarify my earlier post, Index/Match
will work for either numbers or text, Sumproduct only works for numbers (it
is not an array formula, but it accepts array arguments).


"CraigS" wrote:

My SS# are TEXT. I tried itin the databases and got #VALUE as answer.

I then setup two new excel worksheets (i.e. Book1 and Book2) and set up your
little example using the Index formula and still got #VALUE. I also went
ahead and specifically formatted the SS# as TEXT so there was no issue there.
So i'm not sure exactly what is wrong.

"JMB" wrote:

Assuming:
A1:A3 = First Names
B1:B3 = Last Names
C1:C3 = SS Numbers

F1 = First Name
G1 = Last Name

If the SS#'s are formatted as numbers you can use:
=SUMPRODUCT(--(A1:A3=F1),--(B1:B3=G1),C1:C3)

If formatted as text use an array formula (entered w/Control+Shift+Enter)
=INDEX(C1:C3,MATCH(F1&G1,A1:A3&B1:B3))

Change ranges as necessary.

"CraigS" wrote:

The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.

Hi JMB

The Ctrl-Shft-Enter did the initial trick. I also figured out the original
database needs to be sorted on Column A. The one sticky wicket is on the
two database lists sometimes one or both names is spelled differently. With
VLOOKUP I had the option to end it with FALSE so if my search (I know only on
one column) didn't match exactly I would get an #N/A

Is there a way to modify our formula to do the same thing? This way when I
get an N/A I'm able to search the origianl database for the misspelled name
and correct it.

Craig

"JMB" wrote:

One thing that can cause that is by simply hitting Enter after typing in the
formula (which doesn't work for array formulas). As mentioned, you must hold
down the Control and Shift keys, then hit Enter. If done properly, Excel
will put braces { } around the formula.

Are you still getting an error? To clarify my earlier post, Index/Match
will work for either numbers or text, Sumproduct only works for numbers (it
is not an array formula, but it accepts array arguments).


"CraigS" wrote:

My SS# are TEXT. I tried itin the databases and got #VALUE as answer.

I then setup two new excel worksheets (i.e. Book1 and Book2) and set up your
little example using the Index formula and still got #VALUE. I also went
ahead and specifically formatted the SS# as TEXT so there was no issue there.
So i'm not sure exactly what is wrong.

"JMB" wrote:

Assuming:
A1:A3 = First Names
B1:B3 = Last Names
C1:C3 = SS Numbers

F1 = First Name
G1 = Last Name

If the SS#'s are formatted as numbers you can use:
=SUMPRODUCT(--(A1:A3=F1),--(B1:B3=G1),C1:C3)

If formatted as text use an array formula (entered w/Control+Shift+Enter)
=INDEX(C1:C3,MATCH(F1&G1,A1:A3&B1:B3))

Change ranges as necessary.

"CraigS" wrote:

The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.

Yes, MATCH has an optional third argument that you can set. Sorry about
that, I inadvertently left it out of my example.

In the example from my earlier post, use 0 to specifiy it to match exactly.
Also, w/this option you won't need to sort the lists.

MATCH(F1&G1,A1:A3&B1:B3,0)

"CraigS" wrote:

Hi JMB

The Ctrl-Shft-Enter did the initial trick. I also figured out the original
database needs to be sorted on Column A. The one sticky wicket is on the
two database lists sometimes one or both names is spelled differently. With
VLOOKUP I had the option to end it with FALSE so if my search (I know only on
one column) didn't match exactly I would get an #N/A

Is there a way to modify our formula to do the same thing? This way when I
get an N/A I'm able to search the origianl database for the misspelled name
and correct it.

Craig

"JMB" wrote:

One thing that can cause that is by simply hitting Enter after typing in the
formula (which doesn't work for array formulas). As mentioned, you must hold
down the Control and Shift keys, then hit Enter. If done properly, Excel
will put braces { } around the formula.

Are you still getting an error? To clarify my earlier post, Index/Match
will work for either numbers or text, Sumproduct only works for numbers (it
is not an array formula, but it accepts array arguments).


"CraigS" wrote:

My SS# are TEXT. I tried itin the databases and got #VALUE as answer.

I then setup two new excel worksheets (i.e. Book1 and Book2) and set up your
little example using the Index formula and still got #VALUE. I also went
ahead and specifically formatted the SS# as TEXT so there was no issue there.
So i'm not sure exactly what is wrong.

"JMB" wrote:

Assuming:
A1:A3 = First Names
B1:B3 = Last Names
C1:C3 = SS Numbers

F1 = First Name
G1 = Last Name

If the SS#'s are formatted as numbers you can use:
=SUMPRODUCT(--(A1:A3=F1),--(B1:B3=G1),C1:C3)

If formatted as text use an array formula (entered w/Control+Shift+Enter)
=INDEX(C1:C3,MATCH(F1&G1,A1:A3&B1:B3))

Change ranges as necessary.

"CraigS" wrote:

The only complete matching information in both databases is last name and
first name. Database 2 is actually a smaller list pulling info from the
larger database 1. The second database needs to pull social security info
from databse 1 to put in database 2. Because of multiple last names
matching I first want to match the last name in database 1 but somehow then
have an "AND" statement that then requires a first name match at which point
it will pullout the SS # from the row where both last and first names match.