Excel formula to find duplicate value and copy the adjacent cell data

Question

I need a formula to first locate a duplicate value in column A, and once found then copy the values from the adjacent columns B and C...The goal is to have all values for those columns identical.

Example of output (first and last rows):

  A      B        C
930153  ABCD    TEST1
931364  EFGH    TEST2
931288  IJKL    TEST3
930153  ABCD    TEST1

So you want to put a formula in the cells that would copy cells in the same column where column A matches? If so, this is not possible, You will need to put the formula in separate columns. — Scott Craner
Overall, the goal is to find duplicate values in column A, and have those duplicates identically show the same values in columns B&C. I was thinking of on an IF function (i.e., IF column A is equal to a value in the same column, then copy the values of the adjacent columns into B&C). I can paste the formula down column D even and hide the column, as long as it compares the values and copies the proper data. — arcade-relic

OverflowStacker OverflowStacker · Accepted Answer · 2020-06-26T11:03:29

If i understood your comment right, the simplest solution would be

=VLOOKUP($A1,$A$1:$C1,2,0)

It just looks up the the value from current row in column A up to current row and gets the value from column 2 or 3 of the first matched line. You have to adapt the 2.

For the given sample data, this

=VLOOKUP($A1,$A$1:$C1,column()-2,0)

would be an automatic solution for the column.

You can pull the formula downwards and sideways.

Excel formula to find duplicate value and copy the adjacent cell data

1 Answers