Need a formula similar to Vlookup but able to return multiple results for a single search criteria

[cyclonewoody]

Using Excel 2007 and I have a database with several different columns of information I'd like to compile using a lookup type function. The issue I have is that my search criteria (Forman in the database below) is entered multiple times in that database. The Vlookup function stops searching after it finds the first match so I only get 1 piece of the data I need. In the example below I want to set up a sheet for Bob and be able to extract all the data (Job, City, Price, and Size) from Bob's jobs onto just his sheet. The result I want on Bob's sheet from the example below would be:
Job City Price Size
House A XYZ $50,000 1000
House C YYY $25,000 250


How do I get it to return multiple rows of data for all of Bob's jobs into a single sheet?

Job

<tbody>
[TD="width: 19%"] City

[/TD]
[TD="width: 26%"] Price

[/TD]
[TD="width: 19%"] Size

[/TD]
[TD="width: 19%"] Forman

[/TD]

[TD="width: 19%"] House A

[/TD]
[TD="width: 19%"] XYZ

[/TD]
[TD="width: 26%"] $ 50,000.00

[/TD]
[TD="width: 19%"] 1000

[/TD]
[TD="width: 19%"] Bob

[/TD]

[TD="width: 19%"] House B

[/TD]
[TD="width: 19%"] XXX

[/TD]
[TD="width: 26%"] $ 25,000.00

[/TD]
[TD="width: 19%"] 500

[/TD]
[TD="width: 19%"] Jim

[/TD]

[TD="width: 19%"] House C

[/TD]
[TD="width: 19%"] YYY

[/TD]
[TD="width: 26%"] $ 25,000.00

[/TD]
[TD="width: 19%"] 250

[/TD]
[TD="width: 19%"] Bob

[/TD]

[TD="width: 19%"] House D

[/TD]
[TD="width: 19%"] ZZZ

[/TD]
[TD="width: 26%"] $ 100,000.00

[/TD]
[TD="width: 19%"] 2000

[/TD]
[TD="width: 19%"] Jim

[/TD]

[TD="width: 19%"] House E

[/TD]
[TD="width: 19%"] AAA

[/TD]
[TD="width: 26%"] $ 75,000.00

[/TD]
[TD="width: 19%"] 1500

[/TD]
[TD="width: 19%"] Matt

[/TD]

</tbody>

 

[Tom Urtis]

Not sure how your workbook is set up, but you can always filter the data table for Bob (or whatever name you want) in (presumably) column E. Select any cell in the table and hit Alt+D+F+F. Then from the dropdown arrow in cell E1 (again, presuming your header row is 1 and the first column with Job is column A) you can select Bob from the dropdown items and filter that way.

If you are looking for an automated solution based on the same table existing on all sheets (which I doubt is the case) then post back with your workbook's design for a better solution if what I posted here is not practical.

 

[T. Valko]

Using Excel 2007 and I have a database with several different columns of information I'd like to compile using a lookup type function. The issue I have is that my search criteria (Forman in the database below) is entered multiple times in that database. The Vlookup function stops searching after it finds the first match so I only get 1 piece of the data I need. In the example below I want to set up a sheet for Bob and be able to extract all the data (Job, City, Price, and Size) from Bob's jobs onto just his sheet. The result I want on Bob's sheet from the example below would be:
Job City Price Size
House A XYZ $50,000 1000
House C YYY $25,000 250


How do I get it to return multiple rows of data for all of Bob's jobs into a single sheet?

Job

<TBODY>
[TD="width: 19%"] City

[/TD]
[TD="width: 26%"] Price

[/TD]
[TD="width: 19%"] Size

[/TD]
[TD="width: 19%"] Forman

[/TD]

[TD="width: 19%"] House A

[/TD]
[TD="width: 19%"] XYZ

[/TD]
[TD="width: 26%"] $ 50,000.00

[/TD]
[TD="width: 19%"] 1000

[/TD]
[TD="width: 19%"] Bob

[/TD]

[TD="width: 19%"] House B

[/TD]
[TD="width: 19%"] XXX

[/TD]
[TD="width: 26%"] $ 25,000.00

[/TD]
[TD="width: 19%"] 500

[/TD]
[TD="width: 19%"] Jim

[/TD]

[TD="width: 19%"] House C

[/TD]
[TD="width: 19%"] YYY

[/TD]
[TD="width: 26%"] $ 25,000.00

[/TD]
[TD="width: 19%"] 250

[/TD]
[TD="width: 19%"] Bob

[/TD]

[TD="width: 19%"] House D

[/TD]
[TD="width: 19%"] ZZZ

[/TD]
[TD="width: 26%"] $ 100,000.00

[/TD]
[TD="width: 19%"] 2000

[/TD]
[TD="width: 19%"] Jim

[/TD]

[TD="width: 19%"] House E

[/TD]
[TD="width: 19%"] AAA

[/TD]
[TD="width: 26%"] $ 75,000.00

[/TD]
[TD="width: 19%"] 1500

[/TD]
[TD="width: 19%"] Matt

[/TD]

</TBODY>

Try this...

Data on Sheet1 in the range A1:E6.

On Bob's sheet...

These column headers:

A1 = Bob
B1 = Count of Records
C1 = Record Number
D1 = Job
E1 = City
F1 = Price
G1 = Size

Enter this formula in B2. This will return the count of records for Bob.

=COUNTIF(Sheet1!E:E,A1)

Enter this array formula** in C2. This will return the row numbers of Bob's records.

=IF(ROWS(C$2:C2)>B$2,"",SMALL(IF(Sheet1!E$2:E$6=A$1,ROW(Sheet1!E$2:E$6)),ROWS(C$2:C2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in D2 and copy across to G2:

=IF($C2="","",INDEX(Sheet1!$A:$D,$C2,COLUMNS($D2:D2)))

Select C2:G2 and copy down until you get blanks.

 

[cyclonewoody]

I am actually looking for an automated solution. I can set up Bob's sheet with the exact same columns and format as the database sheet. The database sheet may have 500 entries in it and Bob's name may be listed 25 times so I want to see all 25 "jobs" Bob worked on as well as the other corresponding data that goes with his "jobs". I used Vlookup previously and had to move Column E (Bob) into Column A which worked fine, the only problem I have is that no matter how many rows I put in Bob's sheet and how many times I copy and past the Vlookup formula into different rows, it only returns the first row of data associate with Bob, I need it to also list the other 24 pieces of data that are associated with Bob. I'd also like to avoid manipulating the database spreadsheet by sorting mainly because I'll create the same type of sheet for Matt and Jim and I'll constantly update the database with new info so I'd constantly have to sort the database sheet.

 

[cyclonewoody]

Worked perfect! Thanks a bunch!! Now hopefully I can continue to duplicate it on other spreadsheets requiring similar functions.

 

[T. Valko]

Worked perfect! Thanks a bunch!! Now hopefully I can continue to duplicate it on other spreadsheets requiring similar functions.

You're welcome. Thanks for the feedback!