when i drag this to the next combination, i need to change this from
ROWS($1:4) back to ROWS($1:1) in this example case.

That's why you should (if you're able) have the names returned across a row
rather than down a column:

name2..........name7..........name9

Biff

"Minerva" wrote in message
...
Thanks a lot Biff......otherwise i had ended up creating 2 pivots with
names
showing in the other!
However, looks like i need to change the last parameter for each
combination
of the 3(department/category/place)...i.e........ROWS($1:1)
because when i drag this to the next combination, i need to change this
from
ROWS($1:4) back to ROWS($1:1) in this example case.

Thanks anyways.......it helped me ease my work to some extent.
--------------------------------------------------------------------------
"Biff" wrote:

That's an array formula.

It MUST be entered using the key combo of CTRL,SHIFT,ENTER.

Biff

"Biff" wrote in message
...
Hi!

Where do want the names returned? Across a row? Down a column?

And what about the dupes? Do you want the same duplicated names for
each
instance of dept2 sales place1?

Here's a basic formula that will do what you want:

=INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))

Assuming that each table starts in cell A2 of the respective sheets.

When drag copied down will return:

name2
name7
name9
#NUM!

This is just the "basic" formula, no error checking/trapping.

Have you considered filtering?

Biff

"Minerva" wrote in message
...
This is a small snapshot of what i have in 2 sheets:-
Department/Category/Place-----------(3 similar columns for both
sheets)
dept2 sales place1
dept3 support place2
dept1 backend place3
dept2 sales place1
dept3 support place2
dept2 sales place1
dept1 backend place3
dept1 backend place3
dept1 backend place3
dept3 support place2
---------------------------------- and Name column in sheet-2
dept1 backend place3 name1
dept2 sales place1 name2
dept1 backend place3 name3
dept1 backend place3 name4
dept3 support place2 name5
dept1 backend place3 name6
dept2 sales place1 name7
dept3 support place2 name8
dept2 sales place1 name9
dept3 support place2 name10
-------------------
as you can see, there are multiple entries of the same item for each
column.
I need to look up items (in table2) that match (with table1) the
Department
& Category & Place and get Names........for example,
dept2 & sales & place1 (of table1) when looked up brings up three
Names.........name2, name7, name9.
I found some discussions around the topic, but am unable to understand
the
formula, please help in deriving the same.
Many Thanks for your help.