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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Thank you for the help. i will look into making use of this.
0
NorieAnalyst Assistant Commented:
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
LearnReporting 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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eitel DagninIT Security AdministratorAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.