Match Lookup and Copy Column

Hi All,

  • I have 1 main workbook with one sheet only containing user data
  • This worksheet has 11 columns
  • The column headings are as follows
  • Employee Number
  • wbk1
  • wbk2
  • wbk3
.... etc
  • I have another workbook with 10 sheets; each sheet also containing user data
  • Each worksheet has 2 columns - Employee Number & Status ("Active" or "Inactive")
  • Each worksheet is named as follows
  • wbk1
  • wbk2
  • wbk3
.... etc
  • I need to do a lookup with the employee number (found in column A starting at cell A3 in the MAIN workbook) and search through the other 10 worksheets for a match.
  • When a match is found, under the relevant column in the main workbook, the status column should be copied

Example:

Main Workbook has 11 Columns
Column 1 - Employee Number
Column 2 - wbk1
Column 3 - wbk2

We take the Employee Number in Column 1 of our main workbook, open our second workbook, search for the Employee Number in wbk1 and if there is a match, then under Column 2 in the main workbook, we put the text from the status column (which would be either "Active" or "Inactive"). This needs to be repeated for every employee number in the main workbook through every sheet in the second workbook.

If this sounds very confusing, please ask and I will try to make it more simple.

I do not yet have sample code, however I am working on it.
Eitel DagninIT Security AdministratorAsked:
Who is Participating?
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi All,

I seemed to have posted this question twice, just phrased differently.. I do apologize..

Here is the link to the one which should be opened/answered on:

https://www.experts-exchange.com/questions/29092957/VBA-VLookup-Copy-Multiple-Columns-Select-Cells-Where-Data-is-to-be-Pasted.html

Apologies once again.
0
 
NorieVBA ExpertCommented:
Which columns in the sheets in the second workbook have Employee Number & Status?
0
 
Roy CoxGroup Finance ManagerCommented:
You can use VLOOKUP across WorkBooks, both workbooks do not need to be opened.

VLOOKUP from another workbook
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Norie,

Each sheet only has two columns - Employee Number (starting in cell B2) and Status (starting in cell C2)
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Thank you for the help. i will look into making use of this.
0
 
NorieVBA ExpertCommented:
You could put this formula in B3 on the sheet in the main workbook and copy it across and down.

=IFERROR(VLOOKUP($A3,INDIRECT("[Book2]"&B$2&"!$B:$C"),2,0),"")

This assumes the names of the sheets in the other workbook are in row 2 starting in column 2, and 'Book2' is the name of the second workbook, change names/references if that is not the case.
0
 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
HI Eitel Dagnin,

Try this, you can use multiple IF errors as your wish, "=IFERROR(VLOOKUP(A1,[Book1]Sheet1!$A:$A,1,0),VLOOKUP(A1,[Book1]Sheet2!$A:$A,1,0))"

If you share us sample file that will be more helpful and easy to answer your question
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.