I have two excel sheets. I have to merge the two such that the values in one match with the other. For eg.
The first excel, the 2nd excel
1 t 1 tes1
2 5 3 tes3
3 t 4 tes4
4 g
Notice that in the first column of the 2nd excel, 2 is missing, so I want the first excel to look like this,
1 tes1 t
2 5
3 tes3 t
4 tes4 g
I am new to excel. Any help on this will be highly appreciated.
Best Answer
I have placed the data from "the first excel" on Sheet1, and "the 2nd excel" on Sheet2.
The key to this solution is the VLOOKUP() function. First we insert a column.
We then use the VLOOKUP() function to lookup the value of "1" in Sheet2. We specify 2 as the value of the third parameter, meaning we want the value of the 2nd column in the array. Also notice the use of the $ symbols to fix the array. This will be important when we fill down.
Note the contents of Sheet2:
When we fill the formula down, we get matches on all values except for the "2" in cell A2.
In order to display a blank ("") instead of "N/A", as in your problem statement, we can wrap the VLOOKUP() function in the IFERROR() function.
Final Result: