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

Hi,

I have cells with numbers:

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


and other cell C100

and have columns

               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 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?

( 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

( 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?

( 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

( 2018-05-20 17:40:51 +0100 )edit
1

"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.

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

Sort by » oldest newest most voted

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

=INDEX(A7:H11;0;F1)

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.

more