Merge columns in Sheet 1 and Sheet 2 to Sheet 3 - Vlookup

Shwetha Sakaray

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.

Garrett Henke

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.