4 Detailed Methods to Extract Data from One Table to Another in Excel

1. What is the process of getting data from this table to another table?

The process of getting data from one Excel table and transferring it to another table is the act of moving information from one position in an Excel file to another, helping you consolidate information from multiple sources or create summary tables.

2. How to get data from this table to another table in Excel

2.1. Get data from this table to another table in Excel using the VLOOKUP function

The VLOOKUP function allows you to find a value in a table based on a key value and return the corresponding value in another column. Learning how to use this function will help you retrieve data quickly and easily.

VLOOKUP Function Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

• Lookup_value: The value used to search.

• Table_array: The range of data that contains the value to search for. The range must have 2 or more columns. Press F4 after selecting the range to lock the range

• Col_index_num: The column number in the lookup range containing the value to return.

• Range_lookup: The search method. Range_lookup is set to 0 or FALSE to find an exact match, range_lookup is set to 1 or TRUE or omitted to find an approximate match.

Example: Use the VLOOKUP function to retrieve data from the table above.

Step 1: In the Excel worksheet, enter the formula =VLOOKUP(D4,$G$8:$H$11,2,0) in the cell where you want to display the result.

Step 2: Press Enter to display the result and drag the mouse pointer down to display the remaining results as shown in the figure.

2.2. Get data using the MATCH function:

The MATCH function is a powerful tool to find the position of a value in a range of data.

Note:

• This method is used to determine the position of the data to be searched, not to return the data.

• It should only be used when there is a small amount of data, in a small range, and you need to quickly determine the position of the data.

• The MATCH function can be combined with the INDEX function to optimize data retrieval and overcome some of the limitations of the VLOOKUP function.

MATCH Function Syntax: =MATCH(lookup_value, lookup_array, [match_type])

Where:

• Lookup_value: The value used to search.

• Lookup_array: The range of data that contains the value to search for. The range must have 2 or more columns.

• Match_type: The search type. Match_type is set to 0 to find an exact match, 1 or omitted to find the largest value less than or equal to the lookup value, -1 to find the smallest value greater than or equal to the lookup value.

Example: Use the MATCH function to find the position of a value in the following data table.

Step 1: Enter the formula =MATCH(30,E8:E13,0) in the cell where you want to display the result.

Step 2: Press Enter to display the result.

2.3. Get data from this table to another table in excel using the INDEX function

The INDEX function allows you to extract a value from a range of data based on its index number.

INDEX Function Syntax: =INDEX(array, row_num, [column_num])

Where:

• Array: The data range to search.

• Row_num: The row position in the data range that contains the value to search for.

• Column_num: The column position in the data range that contains the value to search for.

Example 1: Use the INDEX function to retrieve data from the following data table.

Step 1: Enter the formula =INDEX(C7:E13,6,2) in the cell where you want to display the result as shown in the formula above and press Enter to display the result.

Example 2: Use the INDEX function combined with the MATCH function to retrieve data from the following data table.

Step 1: Enter the formula =INDEX($G$8:$H$11,MATCH(D4,$G$8:$G$11,0),2) in the cell where you want to display the result.

Explanation of the formula:

The array range must contain the data to be searched and the lookup argument is the range $G$8:$H$11. Press F4 to lock the range.

The row position changes based on the cell in D4, we use the MATCH function to determine the position in the table.

The INDEX function combined with the MATCH function can replace the VLOOKUP function and overcome some of its limitations, such as not being able to search for values to the left of the lookup argument in the data range, or in the case of large data that slows down the VLOOKUP function.

Step 2: Press Enter to display the result and drag the mouse pointer down to display the remaining results as shown in the figure.

2.4. Get data from this table to another table in Excel using Power Query

Power Query is a powerful tool for transforming and combining data from multiple sources. Note:

• This method is used when you have a large amount of data, the number of data spreadsheets across multiple sheets, and repetitive tasks that require multiple operations.

• Power Query is a useful tool for simplifying access, modification, and updating of data from different sources. Power Query is already integrated into Excel 2016 and later versions.

Step 1: Select the range of data you want to retrieve. Then, click on the Data tab, select From Table to open the Power Query feature window.

Step 2: Select the Close & Load option, then select Close & Load, and choose Only Create Connection, and click Load.

Step 3: Right-click on Table1 as shown in the figure, select Merge. Then choose “Xếp loại” (sorting) in Table1, choose “Xếp loại” (sorting) in Table2, and click OK.

Explanation: Here you select 2 columns in both tables that are the same to use as the key to link the two tables together. In this case, it is “Xếp loại” (sorting).

Step 4: In the Queries section, select the Merge1 table you just created. Then, left-click on the position of Table2 as shown in the figure, select “Mức thưởng” (bonus), and then click OK.

Explanation: Here, I select “Mức thưởng” (bonus) because this is the column I need to search for. You can understand that when you select this column, when you select any sorting, the corresponding bonus will be displayed accordingly.

Step 5: We have the following results after performing the steps above in the Power Query feature.

Step 6: To transfer the result you just created to the Excel worksheet, select Expanded Table2 in the Applied Steps section > Select Close & Load > Select Close & Load as shown in the figure.

Step 7: After transferring the data you just created to the Excel worksheet, you will have the following result.

Step 8: Select and filter the results for the “Khá” (good) and “Trung bình” (average) ratings as shown in the figure.

Step 9: And here are the filtered results for employees with “Khá” (good) and “Trung bình” (average) ratings.

3. Conclusion

Above are 4 ways to get data from this table to another table in Excel. I hope this article will help you effectively apply Excel in your work.

If you are looking for a good-value laptop for using Excel and other office software, you can refer to the products at FPT Shop: Laptop chính hãng (Genuine Laptops)

• How to insert a row in Excel, a simple trick that Excel users need to know
• Tips for adjusting Excel print settings to fit the page exactly

You may also like

Simple guide on how to use the ROWS function in Excel that not everyone knows

The ROWS function is a commonly used and familiar function in Excel for office workers. In addition to being used to calculate the number of rows, this calculation function can also be flexibly applied to support combinations with many other common functions such as INDIRECT, IF, SUM, MIN, MAX,…

2 Ways to Convert Currency in Excel That Many Users Still Don’t Know

Knowing how to convert currencies in Excel allows you to perform financial tasks quickly and save time.

Pocket the simple and memorable way to calculate unit price in Excel, boost your work efficiency.

Calculating unit price in Excel is perhaps one of the formulas that many people are looking for in order to quickly and professionally calculate. To calculate unit price, you can use various different methods with Excel functions that are extremely easy to perform. Let’s see what those methods are right away!

What is FileZilla? Guide to installing and using quick FTP connection software.

Using FileZilla allows you to efficiently transfer data between your personal computer and a server. This application has outstanding advantages that help users manage and synchronize files and folders conveniently, while ensuring the safety of data exchange on the web server.

Effortless Samsung to iPhone Data Transfer with Smart Switch

Migrate data from Samsung to iPhone with Smart Switch and enjoy a quick, seamless transition that saves you both time and effort. Switch contacts, messages, photos, videos, and more effortlessly with Smart Switch.