How to Get Data from One Table to Another in Excel (Detailed Guide)

Getting data from one table to another in Excel is an important part of managing and processing information. From using basic functions like VLOOKUP, HLOOKUP to advanced tools like Power Query. The following article will guide users on how to make the most of this tool, making it easier to move data between tables.

I. What is moving data from one table to another?

Getting data from one table to another is the process of copying or moving information from one location to another within the same Excel file.

This is an important and useful trick in organizing and processing data, allowing users to rearrange information according to specific usage needs.

II. How to Get Data from One Table to Another in Excel

This article will guide you through the 5 simplest and fastest ways to transfer data from one table to another, helping users take advantage of all the capabilities of Excel during their work.

1. How to Get Data from One Table to Another in Excel Using the VLOOKUP Function

The VLOOKUP function is a powerful tool that searches and retrieves data in a table based on a specific key value and returns the related value in another column. Mastering how to use this function will enhance your data processing capabilities.

The formula for the VLOOKUP function is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) , with the following components:

• Lookup_value: The value to search for.
• Table_array: The data range that contains the value to find. This range must have at least 2 columns. Use the F4 key after selecting the range to freeze it.
• Col_index_num: The column number in the search range that contains the value to return.
• Range_lookup: The search method. Set range_lookup to 0 or FALSE for exact search; to 1 or TRUE or leave it blank for approximate search.

Example of how to use the VLOOKUP function in an Excel table:

• Step 1: Enter the formula =VLOOKUP(D4,$G$8:$H$11,2,0) into the cell where the result needs to be displayed.
• Step 2: Press Enter to return the result, then drag the mouse pointer down to adjacent cells to apply the formula to the rest of the table.

2. How to Get Data from One Table to Another in Excel Using the HLOOKUP Function

The HLOOKUP (Horizontal Lookup) function is a tool used to search for a value in a horizontal row of a data range and then return the corresponding value from that row.

This function works similarly to VLOOKUP, but instead of searching vertically, it searches horizontally. This method is especially useful when working with horizontally arranged data tables.

The syntax of the HLOOKUP function is: =HLOOKUP(value, table, row_index, [range_lookup]) , with the following components:

• Value: The value to look up to match in the first row of the table.
• Table: The table containing the data to be searched.
• Row_index: The row number in the table containing the data to be searched.
• Range_lookup: Set to TRUE if you want to search for an approximate value or FALSE for an exact search. If nothing is entered, the default is TRUE.

3. How to Get Data from One Table to Another in Excel Using the MATCH Function

The MATCH function is a powerful tool for determining the position of a specific value in a data range. It is not a method of returning data, but only determining the position of that data. This function is suitable when working with a small amount of data, within a limited range, and requires the location of data to be determined quickly.

The MATCH function is often used in conjunction with the INDEX function, forming a powerful pair of tools that overcome some of the limitations of the VLOOKUP function.

The formula for the MATCH function is: =MATCH(lookup_value, lookup_array, [match_type]) , with the following specific components:

• Lookup_value: The value to look up.
• Lookup_array: The range of data that contains the value to look up. This range must have 2 or more columns.
• Match_type: Search type. Use 0 for exact match, 1 or nothing to find the position of the value less than or equal to the search value, -1 to find the position of the value greater than or equal to the search value.

Example of how to get data from one table to another in Excel using the MATCH function:

• Step 1: Enter the formula =MATCH(30,E8:E13,0) into the cell where the result needs to be displayed.
• Step 2: Press Enter to execute the function and display the result.

4. Transfer data from one table to another using the INDEX function

The INDEX function in Excel is used to extract values ​​from a range of data, based on a specific row and column number.

The formula for the INDEX function is: =INDEX(array, row_num, [column_num]) , with the following elements:

• Array: The range of data to search.
• Row_num: The row number in the data range that contains the value to extract.
• Column_num: The column number in the data range that contains the value to extract.

Example 2 ways to get data from one table to another table in Excel using INDEX function:

Method 1: Extract data with a single INDEX function: Enter the formula =INDEX(C7:E13,6,2) into the desired cell and press Enter to extract the result.

Method 2: Combine INDEX and MATCH functions to extract data:

• Step 1: Enter the formula =INDEX($G$8:$H$11,MATCH(D4,$G$8:$G$11,0),2) into the cell where you want to display the result.
• Step 2: Press Enter to extract the result and drag the mouse down to apply the formula to the next cells.

The INDEX function combined with MATCH can be an alternative to the VLOOKUP function, especially when looking up a value to the left of an argument in a data range or in cases where the VLOOKUP function is slow when processing large data.

5. How to Get Data from One Table to Another in Excel Power Query

Power Query is a tool that specializes in searching and consolidating data from many different sources. This is a suitable method when processing large data sets with a number of elements spread across many sheets and in cases where a series of operations need to be performed repeatedly.

With the ability to simplify accessing and editing data from different sources, Power Query is an important tool and has been built into Excel since version 2016.

Procedure for using Power Query to transfer data from one table to another in Excel:

• Step 1: Select the data area to process, then go to Data and select From Table to open the Power Query window.
• Step 2: Select Close & Load , then select Only Create Connection and click Load .
• Step 3: Right-click on Table1 , select Merge , then select the Rating column in both tables and click OK . This is the step to select two identical columns to act as the link key between the two tables.
• Step 4: In the Queries section , select the Merge1 table , then click on Table2 and select the Bonus column , then click OK . Here, select the Bonus column because that is the column to search.
• Step 5: View the results after performing the steps in Power Query .
• Step 6: To transfer the results to the Excel workbook, select Expanded Table2 in the Applied Steps section , then select Close & Load and finally click Close & Load .
• Step 7: The data transfer result will be displayed in the table.
• Step 8: Filter out the results of “Good” and “Average” ratings by clicking on Options in the Rating box and selecting two rating criteria as Good and Average . 
• Step 9: Now, employees with “Good” and “Average” ratings will be displayed in the table.

Above are the 5 detailed ways to transfer data from one table to another in Excel. I hope this article helps you in your work and studies. If you have any suggestions, feel free to leave a comment below, and don’t forget to share if you find it helpful.

See more: 5 ways to delete duplicate data on Excel that accountants should know