Pull Data from one sheet to another

[Dax Arroway]

This should be a pretty easy formula but I can't figure it out. Will someone
please help?

In SQL I'd say GET SS# from Sheet1 where NAME EQ Sheet2!A1.
In other words on sheet2 I have names in column A. I also have this
person's name in Sheet1 along with other personal information (names on
different columns). In column B of sheet2 I need a formula that compares the
name in sheet2!A1 to the names (columnC) of Sheet1 and pulls the SS# of that
person's record/row. In order to make sure that it's the right person (there
could be 4 jim smiths for instace) it would also need to depend upon a blank
in column G. (The list is generated from names in sheet1 with blanks in
column G. If G has data, the row is ignored.)

So to recap, I need a formula that compares the name in sheet2 to sheet1 and
pulls the SS# from column D in sheet1 where the cell in row G is blank.

Any takers?

Thanks millions in advance.
--Dax

 

[T. Valko]

Where are the SS#s?

Assume they're in column H.

Array entered** :

=INDEX(Sheet1!H2:H10,MATCH(1,(Sheet1!C2:C10=A1)*(Sheet1!G2:G10=""),0))

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