How often does this situation happen to you? You have 2 spreadsheets full of data and they need to be combined into one. What makes it even more difficult is that both tables are not organized in the same way, leaving you with data all over the place. There's an easy way to fix this.
VLOOKUP is an easy way to compile data into one spreadsheet, even when the data is scattered throughout multiple spreadsheets. It's one of the many tools Excel uses to help users keep better organized, but it can be very under utilized. Here's how to use it:
1. Start with a somewhat blank spreadsheet, one that would have the basic categories that you want the data organized into.
2. Open up the other spreadsheets that hold the data you would like to transfer over.
VLOOKUP works by taking the data from one spreadsheet and easily moving it to another, blank spreadsheet.
3. Select the cell where the new information will be input. Type =VLOOKUP. Excel will auto-finish the search for you. Double click on VLOOKUP.
4. Click Insert Function next to the text bar; the icon that looks like fx.
A new window specify the values it should be looking for. Be sure to click inside each box before inputting the information; the information will then be automatically input into those boxes.
5. In Lookup_value, select the cell with the matching information in the other spreadsheets. In the example listed, it would be A2, the names of the fruits.
For Table_array, select the all the information in the spreadsheet. An easy way to do this is by clicking the triangle in the upper left hand corner of the spreadsheet.
Col._index_num is the number of the column from the beginning of the spreadsheet. In this case, it would be 2.
Range_lookup makes sure that the data is exact. The only choices to input here are 0 and 1. Zero gives an exact match up of information; one finds the closest matching value and inputs that. This could leave room for errors.
Then click OK. The information will be input into the empty cell.
6. Highlight the rest of the column under the newly filled cell to auto-fill all the information. Or double click on the small square in the bottom right corner of the highlighted box to fill in the whole column.
Double check the information to make sure it is all correct. You have now input information from one spreadsheet to another.
7. Repeat these steps to fill in the remaining information for your table.
Congratulations! You now know how to use VLOOKUP for Microsoft Excel to easily input data from multiple spreadsheets into one.