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.