Peo,

many thanks for the help but the report need to be continually updated
either by change to the question i.e. Dept=admin to Dept=IT, or as a new row
is created.

The advanced filter doesn't seem to work correctly probably me :) and it
will not copy to another sheet??

I have had a go with your formula but just getting a blank field returned??

many thanks and I'll just have to keep trying. Just wish they would let me
do a course.

Colin
"Peo Sjoblom" wrote:

This what filters are made for, you could set up an advanced filter and use
"Dept" and "Admin" as
a 2 cell criteria range than you can select copy to another location in the
filter setup.
If you want formulas you would need 5 versions (where one would change the
INDEX part in each of them) of the same rather complicated array formula,
then those 5 formulas need to be copied down as far as needed. An example of
such a formula can be found here (you can also download a sample workbook)

http://nwexcelsolutions.com/advanced..._page.htm#Home


it's number 6

However the best way is IMHO definitely filtering



--


Regards,


Peo Sjoblom


"UKMAN" wrote in message
...
i have a table with about 200 rows and need to match a single keyword
question to a field in a row then copy that rows data to another summary
table.

e.g.
Question: Dept = Admin

Table: (The data is in an array name of "budlevel")
Dept, Team, Name, Course, Cost, Status
Admin A1 Sid x 1 Booked
IT IT1 Bill y 1 Res
Catering C1 Mike x 1 Booked
Admin IT2 Colin x 1 Booked
Admin IT1 Jane x 1 Res

Summary:
Team, Name, Course, Cost, Status
A1 Sid x 1 Booked
IT2 Colin x 1 Booked
IT1 Jane x 1 Res

I am using the following formula =VLOOKUP(Question,Bud_level,2,0) which
will
give me the name in one line but I cannot figure out how to make it search
all the rows and bring back the example summary

I have had help that says use advanced filter but I need to keep changeing
the "question" field and then print out which this doesn't allow me to do.

Cheers
In advance for any and all advice.