Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
take a look at this page –  Raystafarian Mar 21 '12 at 10:28
add comment

1 Answer

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)),"")

enter image description here

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.