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