JOIN operation on Excel lists

B

bibikoff

Can I make an SQL-like JOIN operation on 2 Excel -lists without any
macros?

For example:

I have "Employees" list

Code:
--------------------

name depart_Id
=======================
Ivanoff 34
Petroff 36
Sidoroff 34
Smirnoff 34

--------------------


I have "Departments" list

Code:
--------------------

dep_name id
=======================
Accounting 34
Marketing 36

--------------------


How can I get joined list "Employees with they departments"?

Code:
--------------------

name depart_id dep_name
==========================================
Ivanoff 34 Accounting
Petroff 36 Marketing
Sidoroff 34 Accounting
Smirnoff 34 Accounting
 
S

SteveG

Try this in the dep_name colum of list 3.

=INDEX(Departments!$A$2:$B$3,MATCH(List3!B2,Departments!$B$2:$B$3,0),1)

This assumes that you have the list of names and dep_id's in List 3 and
you are trying to retrieve the dep_name from the Departments list.

Does that help?

Steve
 
B

Bob Phillips

=INDEX(Employees,ROW(A2),1)&"
"&INDEX(Departments,MATCH(INDEX(Employees,ROW(A2),2),D1:D10,0),1)

where D1:D10 is the second column of Departments.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
B

bibikoff

SteveG said:
Try this in the dep_name colum of list 3.
=INDEX(Departments!$A$2:$B$3,MATCH(List3!B2,Departments!$B$2:$B$3,0),1)
Steve
Click to expand...

Bob said:
=INDEX(Employees,ROW(A2),1)&"
"&INDEX(Departments,MATCH(INDEX(Employees,ROW(A2), 2),D1:D10,0),1)
where D1:D10 is the second column of Departments.
Click to expand...

Bob, Steve, thanks for your answers.
But I think they are too difficult to "lame user". He doesn't know
anything about formulas. He can't use these methods. I can't explain to
him how to solve his problem with this approach.

Maybe Excel have some menu item with same effect? Or maybe tricky
combination of other Excel features gives same effect?

Sorry for my English, one more time :)
 
B

Bob Phillips

Sorry mate, I think you are stuck with this sort of formula, or VBA which is
even worse. Can't you implement it for him?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top