Maruf Islam
Often in Excel users need to move data from one cell to another. Whatever may be the reasons; referencing, lookup, criteria matched, etc. users normally Copy and Paste the cell data into another cell. However, in this article, we discuss Excel formula to move data from one cell to another.
Let’s say we have a dataset that contains Products with Sold Quantity and Unit Price.
In this article, we demonstrate Excel formula to move data from one cell to another.
Download Excel Workbook
5 Types of Excel Formula to Move Data from One Cell to Another
Formula 1: Using Direct Reference to Move Data from One Cell to Another
One of the easiest formulas to move a cell data is to directly reference it in another cell.
Step 1: Type = Cell Reference in any blank cell to move or display one cell data into another.
Step 2: Press ENTER to apply the formula in the respective cell. You see Excel displays the mentioned cell (i.e., C11) data in another cell (i.e., G5).
You can directly refer to as many cells as you need. But it’s convenient when you deal with a handful of entries unless it’s tiring.
Read More: How to Move Down One Cell Using Excel VBA (with 4 Useful Applications)
Formula 2: Moving Cell Data Depending on Criteria Using IF Function
Most of the time users rely on one or more than one entry to move or fetch data. The IF function offers a logical_test to conduct moving one cell data to another.
Let’s assume, we have the Sales data for a certain date. We want the Total Sold Quantity if the Today’s Date matches the Given Date (i.e., 4/26/2022).
Step 1: Paste the following formula in any blank cell (i.e., F8)
=IF(TODAY()=F5, C17,"")In the formula, the TODAY function results in today’s date. Therefore, TODAY()=F5 executes the logical_test. If the logical_test results in TRUE then Excel displays C17 data unless blank.
Step 2: Hit ENTER to apply the formula and Excel moves the cell data to another as depicted in the following picture.
Read More: How to Move Cells without Replacing in Excel (3 Methods)
Similar Readings
- How to Move Highlighted Cells in Excel (5 Ways)
- Rearrange Rows in Excel (4 Ways)
- How to Move Rows Up in Excel (2 Quick Methods)
Formula 3: Move Matched Data Using INDEX-MATCH Formula
Now, moving data resembles INDEX–MATCH data where specific cell data is moved after matching a criterion. Suppose we want to move or fetch the Unit Price of a given Product. We use the INDEX-MATCH formula to match and move the Unit Price data to another cell. The dataset looks similar to the below screenshot.
Step 1: Write the following formula in any blank cell (i.e., H5).
=INDEX(E5:E16,MATCH(G5,C5:C16,0))The formula takes E5:E16 as an array, MATCH(G5,C5:C16,0) as row_num for the INDEX function. Also, the MATCH function has G5 as lookup_value, C5:C16 as lookup_array, and 0 as exact_type (i.e., Exact).
Step 2: After pressing the ENTER key, you see it moves the Unit Price of the given Product (i.e., Bran).
Read More: Move One Cell to Right Using VBA in Excel (3 Examples)
Formula 4: Fetch Matched Data Using LOOKUP Function
Similar to INDEX-MATCH, the LOOKUP function can move certain data of a given entry. Before the application of the formula the worksheet looks like this:
Step 1: In an adjacent cell (i.e., H5), Type the following formula.
=LOOKUP(G5,C5:C16,E5:E16)The arguments in the formula define G5 = lookup_value, C5:C16 = lookup_vector, E5:E16 = result_vector.
Step 2: In order to apply the formula, use the ENTER key and it moves the matched data to another cell as depicted in the below image.
Read More: How to Shift Cells Right in Excel (4 Quick Ways)
Similar Readings
- How to Shift Rows Down in Excel (3 Simple & Easy Ways)
- Use the Arrows to Move Screen Not Cell in Excel (4 Methods)
- How to Move Rows in Excel (4 Simple & Quick Methods)
- Fix: Excel Cannot Shift Nonblank Cells (4 Methods)
Formula 5: Excel VLOOKUP Function to Move Data from One Cell to Another
Alternatively, we can use the VLOOKUP function to perform the same function as INDEX-MATCH or LOOKUP functions did. The difference between VLOOKUP and the other functions is that VLOOKUP needs to place the lookup_value at the first column of the table_array. Therefore, consider the following image as the data setup.
Step 1: Place the following VLOOKUP formula in any adjacent cell H5.
=VLOOKUP(G5,B5:E16,3,FALSE)Inside the formula, G5 = lookup_value, B5:E16 = table_array, 3 = col_index_num, False = range_lookup (i.e., Exact Match).
Step 2: After the formula insertion, Hit ENTER. In a moment, Excel displays the Quantity on a given Date as shown in the below picture.
Read More: How to Shift Cells Up in Excel (5 Quick Ways)
Conclusion
In this article, we demonstrate multiple Excel formulas to move data from one cell to another. Since we are not discussing Copying one cell data to Paste it into another cell, we avoid demonstrating those Excel features. Hope you find these formulas fit for your dataset. Comment, if you have further inquiries or have anything to add.
” title=”
FWIW, I would argue that this article is poorly named. You are not moving data. When you move a box from point A to point B, the box is no longer at Point A when you are done. When you move a file in your file system rather than copy it, it was one place, now it is someplace else. In all of your examples, the data is still present in the original location, so you did not move the data. You do several variations so I don’t know if you would say referenced, or queried, or looked up, but referenced is probably closest. I was disappointed because I actually want to move data. I have many rows with a different number of columns, but I know the last 3 columns are consistent, so I want to write a formula that says “find the last non-blank cell (which I can do) and MOVE IT to this cell. Then I can do that again, because now I know what the new last cell is and so on. That would allow me to line up the constant columns. Anyway, not asking you to solve my problem, just explaining mu use case for an actual move and explaining why I believe your title is not technically accurate and it lead me astray since I was actually looking to move a cell.
Regards,
Bill
Hello Bill Allcock, Excel formulas can’t move data. So, we’re making a copy of the value. You need to use VBA to do so. Moreover, you can send us your sample Excel file to [email protected] and we will try to solve your problem using VBA.