How do I update an excel table with data from other table?

Question

I'm fairly new to excel.

I have two tables, one is the large main table that i want to update. Another is a smaller one that contains a few updated rows.

I need to update the "length" , "Y/N" , "Date Pulled" Columns in the main table by using the data from the small table that matches with the "Cable ID" Column.

Example:

Find Cable 235.10-W1024 in the main table and update the Length, Y/N, and Date to show 32.00, Y, 12-Jun-17. So it matches the smaller table.

Answer 1

This can be done with a fairly simple vlookup. The syntax for vlookup is =vlookup(Lookup_value,Table_array,Col_index_num)

So in your case, you will want to enter the vlookup into the main table. The lookup value will be the cable cell because that is the cell that you want to match between tables (Use the $ for absolute reference on the column so you can drag down later), the table array with be the rows on the smaller table (Use $ for absolute reference for the column and row here) and then the col index will be the column in the table that you want to pull. So for length, it is 2, SO# is 3, installed is 4, and date is 5.

In the end, your formula should look something like this:

=VLOOKUP($A2,$I:$M,2)

*Note: My "small table" is I:M, the table array should be where your small table is located. If it is on another spreadsheet it will be 'Other Spreadsheet Name'!$I:'Other Spreadsheet Name'!$M

Then from there you can drag down the main table. For the SO#, installed, and date, simply copy the formula over and change the index number.