Skip to content

How to formula move data from column B to D when columns A and C match in Microsoft Excel

    This guide will provide you with the Microsoft Excel formula for when columns A and C match and you need to move numbers from column B next to column D. If that seems confusing – don’t worry – we’ve got you covered with a simple formula!

    This is a situation for when you need data to remain consistent for items when one column may be sorted differently even though it contains the same original data.

    We’ve got a screen shot to clearly show the situation and then the formula to solve the problem in this guide.

    Example of the column situation

    So, let’s say for Fruit, you’d like to move 6 next to Fruit on column D, and then have the other respective numbers do the same. This will keep 6 mapped to Fruit.

    What’s the formula

    You’ll use a VLOOKUP solution to the problem here:

    1) Click in cell D1

    2) Paste =VLOOKUP(C1,$A$1:$B$6,2,0)

    3) Drag down from the corner of the cell to apply to the column.

    That’s it!

    Take a look after dragging and your problem will be solved. You can now expand this to much larger data sets by using the same formula.

    It’s helpful to keep a digital notepad of all your favorite Excel formulas with a little summary of each so you don’t forget. No one is expected to remember them all.

    If you like Excel formulas to solve problems, check out some keyboard shortcuts to make your Excel life easier too: