A simple Lookup: 

          A     B         C      D           
    1  LName   FName  born     Place         
    2  Kennedy John F.19170529 Brookline, MA 
    3  Bush    George 19460706 New Haven, CT 
    4  Carter  James  19241001 Plains, GA    
    5  Reagan  Ronald 19110206 Tampico, IL   
    6  Bush    G.H.W. 19240612 Milton, MA    
    7  Ford    Gerald 19130714 Omaha, NE     

AB1: =VLOOKUP("Carter",$A$1:$D$1000,2,) is suitable when only "James" has to be returned. It is a bad choice beyond that. Why?

Explanation: 

The basic information where "James" comes from is not stored by using =VLOOKUP(). It gets lost. It is record n° 4 (headers also count as records here). If "date of birth" and "place" are needed the record must be found again. Imagine we talked about 70,000 rather than 7 presidents, you would have to go through 40,000 comparisons again TWICE.

Solution: 

Y2: "Carter" (so you change the name in a separate cell, not within the following formula).
Z2: =MATCH($Y2,$A$1:$A$1000,) results 4. With
AA2: =INDEX($A:$D,$Z2,COLUMN(A2)) - copied right until AD2 - you get all 4 presidents data without searching them.

The search takes place only once, in Z2. =INDEX() has sufficient information for direct access to unlimited fields of the record. Excel needs only 40.004 matches or accesses instead of 160.000 before which is only a forth!

More than one record matches: 

What happens if all presidents "Bush" are be asked for? That means we would like to see 2 records by 4 fields. It has to be a formula solution, not auto filtering or special filtering.

        Y   Z   AA     AB      AC      AD           
    1  Bush   LName   FName  born     Place         
    2       3 Bush    George 19460706 New Haven, CT 
    3       6 Bush    G.H.W. 19240612 Milton, MA    
    4     #NA #NA     #NA    #NA      #NA           

Y1: Bush
Z2: =MATCH($Y$1,INDEX(A:A,Z1+1):A65000,)+Z1
AA2: =INDEX($A:$D,$Z2,COLUMN(A2))

Copy Z2:AA2 down to row 100 (if you want) and then fill AA2:AD100 right (please note the exact ranges!).

To stealth the formulas of record 3 and following, select the whole table (Ctrl-A) and format it conditionally by
"Formula is" =OR(ISERROR(A1),($Z1=$Z65536)*($Z1>0)). Fit font color to background color for invisibility.

Both you can study in the German example Rechnung (invoice), also saved here as Google Spreadsheet. Please enter 1, 2, 3 or 4 into the yellow cell.