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.