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.