How to copy column based on cell value to another sheet?
Author: SunLast Modified: 2020-05-25
Supposing you have a range of data in Sheet 1, and each column header is a date, and now you want to copy the whole column from Sheet 1 to Sheet 3 based on the date in Cell A1 of Sheet 2 as below screenshot shown. In this tutorial, I will introduce a trick on solving this job in Excel.
Copy column to another sheet based on cell value with formula
Here is an lNDEX formula in Excel can help you to quickly extract the column data to another sheet based on a cell value.
1. Select a blank cell which you want to place the extracted column, for instance, Cell A1 in Sheet 3, and then enter this formula =INDEX(Sheet1!$A1:$E1,MATCH(Sheet2!$A$1,Sheet1!$A$1:$E$1,0)) and press Enter key to extract the first data, then drag auto fill handle down to until zero appears. See screenshot:
2. Then remove the zero cells as you need.
Note: in the above formula, Shee2!A1 is the cell you want to copy based on, Sheet1!A1:E1 is the range you want to find the criterion, you can change them as you need.
Tip.If you want to quickly select cells based on ctriteia, please try to use the Kutools for Excel’s Select Specific Cells as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
This comment was minimized by the moderator on the site
Спасибо, конечно, за формулу, вы какой-то странный человек.
Из 1 формулы аж целый урок.
+ новичку вообще не понятно что с чем и почему.
Могли бы для простоты понимания хоть листы нормально и коротко проименовать, например, первый лист "L-1" или ещё как-то.