Need formulas to return multiple data matches

[JeffC]

Hi,

I have two spreadsheet databases (each in separate sheets) with the
following information in them: the first one contains records about
"projects" with each project having a unique number. The second spreadsheet
database contains details about contractors assigned to a project. In this
database, a "project" may have multiple records - one for each contractor
assigned to the project.

I'm trying to create a "report" in a sheet in which I can put the project
number in a cell, and formulas will return all the contractors assigned to
that project. I have tried to use =vlookup() and =match()... and they work
to return the first record...

How can I set formulas up so that I can return all the contractors assigned
to a project? In other words... let's say the max # of contractors assigned
is 10. Then I would like to have 10 cells in my report... each with a
formula (similar to match() or vlookup()) that will return the contractors
assigned to that project from the correct database.

Any ideas on how to set this up would be very helpful and much appreciated -
thanks in advance.

 

[Bernie Deitrick]

Jeff,

In cell A1 of your Report sheet, enter the project number. Then in any cell, array enter (enter
using Ctrl-Shift-Enter) the formula

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)>=ROWS($A$1:A1),INDEX('Data Sheet'!A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

and copy down and over to create your extracted table. Delete the columns that you don't need
afterwards.

I wrote the formula for a database on a sheet named "Data Sheet" with project numbers in column A of
that Data Sheet.

HTH,
Bernie
MS Excel MVP