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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!