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