Merge columns in Sheet 1 and Sheet 2 to Sheet 3 - Vlookup
I have Sheet 1 with columns Employee ID, Employee Name, Employee Address.
I have Sheet 2 with columns Employee ID, Salary, Address, Status.
I need a Sheet 3 which has columns Employee ID, Employee Name, Salary, Status from sheet 1 and sheet 2.
I am trying vlookup : =VLOOKUP({Test Range 1}, {Sheet 1 Range 6}, 2, false)
This is working for one cell.
But I want it to pick up the "Search value" dynamically from Sheet 2.
Could you please help me how I can write vlookup to take the search value from Sheet 2 dynamically and I should not be writing the vlookup for each cell.
Answers
-
I would use an Index/Match formula to pull the information into a single sheet. The formula would look like this: =INDEX({Value Range}, MATCH([Employee ID]@row, {Employee ID Range}, 0))
Use the Employee ID as your lookup value and then index the Employee Name, Salary, and Status. You would need to write the formula for each column (however you are really only changing the value range and employee id range) then make the formula a column formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!