# Excel - Using VLOOKUP to create a summary table

I need a little help with some Excel.

```Employee  Locations     Hours   OT
Mr.One    Station 1     40      6
Mrs.Seven Station 2     30      6
Mr.Two    Station 3     30      4
Mr.Three  Station 4     40      4
Mrs.Eight Station 1     32      6
Mr.Four   Station 2     32      7
Mrs.Nine  Station 3     40      6
Mr.Five   Station 4     40      7
Mr.Six    Station 1     25      2
Mrs.Ten   Station 2     40      3
Mr.Eleven Station 3     60      1
```

I have spreadsheet with two worksheets. One is the data sheet (shown above), and the other sheet is a summary that has the `Locations` column as data validation list. I want to use the data validation list to pull all the people and info from a specific location. I tried using `VLOOKUP()` but I only know how to use it to pull one person at a time, not a group of records specific to a location.

-
take a look at this page – Raystafarian Mar 21 '12 at 10:28

If you are using Excel 2007 or later, you can use the following solution (the `IFERROR` function is the limiting factor). In the sample summary table shown in the screen capture below, I used this formula, filled down several rows, to return the Employees who matched the location chosen from the drop-down in B2:

``````=IFERROR(INDEX(Data!\$A\$1:\$A\$12,SMALL(IF(Data!\$B\$1:\$B\$12=Summary!\$B\$1,ROW(Data!\$A\$1:\$A\$12),2000000),ROW()-3)),"")
``````

This formula must be entered as an array formula. Do so by pressing Ctrl+Shift+Enter.

In the hours column, this array formula was filled down:

``````=IFERROR(INDEX(Data!\$C\$1:\$C\$12,SMALL(IF(Data!\$B\$1:\$B\$12=Summary!\$B\$1,ROW(Data!\$C\$1:\$C\$12),2000000),ROW()-3)),"")
``````

One thing to note about the formulas is the last argument in the `SMALL` function `ROW()-3`. This returns `1` for the first record in Row 4. If your summary table starts at a different row, you will need to adjust this argument so that it returns `1` for the first row of your table. For example, if your table starts in Row 2, you will need to change the argument to `ROW()-1`.

-