JOIN operation on Excel lists

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

For example:

I have "Employees" list

Please Login or Register  to view this content.

I have "Departments" list

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Sorry for my bad English

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

=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)

"bibikoff" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> --------------------
>
>
> Sorry for my bad English
>
>
> --
> bibikoff
> ------------------------------------------------------------------------
> bibikoff's Profile:
http://www.excelforum.com/member.php...o&userid=34299
> View this thread: http://www.excelforum.com/showthread...hreadid=540692
>

Originally Posted by 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)
Steve

Originally Posted by 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.

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

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)

"bibikoff" <[email protected]> wrote in
message news:[email protected]...
>
> SteveG Wrote:
> > 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
>
> Bob Phillips Wrote:
> >
> > =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.
> >
>
> 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
>
>
> --
> bibikoff
> ------------------------------------------------------------------------
> bibikoff's Profile:
http://www.excelforum.com/member.php...o&userid=34299
> View this thread: http://www.excelforum.com/showthread...hreadid=540692
>