| View previous topic :: View next topic |
| Author |
Message |
sriniwas67
General User
Joined: 05 Mar 2012
Posts: 9
|
 Posted: Tue Jun 19, 2012 3:55 am Post subject: How to use vlookup function with multiple worksheets |
 |
|
Sample work sheets
Sheet 1 Column A
Emp Name
Jhon
Abraham
Jack
Sheet 2.Column A
Emp Name
Abraham
Jack
Jhon
Clarke
Sheet 2.Column B
Emp.No
245
376
465
595
Request Code for the following
To update sheet 1 with emp no & also the name of Clarke and his emp No from sheet 2. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Jun 19, 2012 7:41 am Post subject: |
|
|
X1 =N(MATCH(val; vector1;0))
Y1 =N(MATCH(val; vector2;0))
Z1 =INDEX(colB ; IF(X1;X1;Y1))
of course you should organize your data more efficiently.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8976
Location: Lexinton, Kentucky, USA
|
| Posted: Tue Jun 19, 2012 12:33 pm Post subject: |
|
|
Sheet1, B2
=VLOOKUP(A2;Sheet2.A$2:B$5;2;0)
and copy down the column. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1844
Location: Sydney, Australia
|
| Posted: Tue Jun 19, 2012 3:54 pm Post subject: Re: How to use vlookup function with multiple worksheets |
|
|
| sriniwas67 wrote: | | ...& also the name of Clarke and his emp No from sheet 2. |
http://www.mediafire.com/view/?h713ltjh76e3y3p
has an array formula that adds Sheet2 Emp Names not already on Sheet1, then the VLOOKUP formula adds the appropriate Emp.No.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
