Ask Your Question

IF cell value are the same then copy value from one cell to other cell

asked 2018-05-20 13:07:49 +0100

unknown1001 gravatar image

updated 2018-05-20 13:10:01 +0100


I have cells with numbers:

C1=4   C2=5    C3=6    C4=7   C5=8

and other cell C100

and have columns

    1    2    3    4    5    6    7    8 
   54   21   54   54   21   21   32   84
   54   21   64   54   26   27   38   94
   58   71   34   14   21   21   32   84
   54   21   54   54   21   11   32   84

and i need formula if cell C1 have same value as other cell C100 then copy data from column 4 to other new column

edit retag flag offensive close merge delete


Hi. If C2 = C100 should column 5 be copied then? If C3=C100, then column 6 and so on? Should data from your columns be copied to the same column overwritng previous values, or there should be different column for every column from original data source?

SM_Riga gravatar imageSM_Riga ( 2018-05-20 15:47:33 +0100 )edit

New column from original data source, i would like to choose from insert cell number which column should be copied to new column

unknown1001 gravatar imageunknown1001 ( 2018-05-20 17:09:04 +0100 )edit

So please correct me if I am wrong. You want to create ONE column (I will name it New Column) which has to be filled with values from your original data, based on your choice. If you enter 8 in your C100 cell, the New Column should be filled with values from Column 8. If then you enter new value in C100, let say 6, then the values in New Column should be overwritten with data from column 6. Is this correct?

SM_Riga gravatar imageSM_Riga ( 2018-05-20 17:30:01 +0100 )edit

Yes, thats right i would like to copy whole column depends what number i put in cell C100

unknown1001 gravatar imageunknown1001 ( 2018-05-20 17:40:51 +0100 )edit

"copy to" is the wrong perspective for a spreadsheet. You have to put a formula into the target cell, which fetches the values from the source column. The function to do this is INDEX in your case. First parameter is the source range, second is the row number and third the column column from that source range. Row and column number are relative to the source range, not to the spreadsheet. To copy a entire column, leave the row empty and insert the formula as matrix formula.

Regina gravatar imageRegina ( 2018-05-20 17:50:10 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-05-20 18:38:26 +0100

updated 2018-05-20 18:43:24 +0100

So here will be solution using INDEX function, based on Regina's suggestion. Spreadsheets are about functions, so let them help you ;)

image description

Create you New Column wherever you want it to be. In the first cell of this column insert an array formula:


Remember, to enter an array formula, you need to press Shift + Ctrl + Enter. In this case A7:H11 is a reference to the original data source range, change it to whatever you need. Leave 0 as second argument, it will tell Calc to use the whole column within range, not only specific row. The most important is the third argument - it tells Calc which column should be referenced. In my example, the value is taken from F1 cell, you can use your C100 cell for reference. So, if you enter 1, the values will be referenced from the first column of your data range, 2 - from second and so on. My example DOES NOT use your C1:C5 cells, cause I do not know if you really need to use them.

edit flag offensive delete link more


Amazing! Thank you so much!

unknown1001 gravatar imageunknown1001 ( 2018-05-20 19:00:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-20 13:07:49 +0100

Seen: 1,043 times

Last updated: May 20 '18