In **Microsoft Excel**, alignment is basically the positions of one’s data in the cells. Excel has its own default alignment. But we can easily change, edit or align two sets of data in Excel by following those ways shown below.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**3 Ways to Align Two Sets of Data in Excel**

There are a few simple techniques for controlling cell behavior. Excel has the flexibility to set the data as per the requirements.

**1. Line up Two Sets of Data in Excel Using VLOOKUP Function in Excel**

In Excel, the **VLOOKUP Function** means “Vertical Lookup” by which we can search for a certain value in a column. We can also align the datasets by using this function. In the following example, there are two sets of data. The first dataset contains some product names, the prices of the products.

And the second set of data contains the same products which are on column B in dataset 1 but some are missing. So, now we will see how we can align those two sets of data.

We are going to align those two data and merge them in a new set of data named Aligned Dataset.

**STEPS:**

- First, In cell
**I5,**we need to write down =VLOOKUP( now it’s asking which value you are looking for. So, we are looking for cell**B5**then pressing comma (**,**) from the keyboard. Now select the second dataset then press cell**F4**. We will put a**$**sign in the**F4**cell, which will freeze them. Then press**1**,**0**).

So the function we need to write is:

`=VLOOKUP(B5,$E:$G,1,0)`

And this is for the first column. If we want to fetch the second column, just select the whole formula and in cell **J5 **paste the formula. Change it by

`=VLOOKUP(B5,$E:$G,2,0)`

And for the third column in the **K5 cell**,

`=VLOOKUP(B5,$E:$G,3,0)`

- If the name doesn’t match it shows
**Not Applicable**(**N/A**). To avoid**N/A**, let expand the formula further

`=IFERROR(VLOOKUP(B5,$E:$G,1,0),””)`

- Once we enter all the columns, copy the whole formula and select the rest of the columns and simply paste it. Now, we can see that the dataset has been aligned according to column
**B**.

**2. Aligning Duplicate Values in Two Sets of Data with IF Formula**

The **IF function** is the most popular function in Excel. With this, we will make logical comparisons and align the duplicate values in two sets of data.

In the following example, there are two sets of data, the first dataset contains some product name which is in **Product List 1** and the second dataset also contains some product name which is in **Product List 2**. Some products are similar so we are going to align those products.

**STEPS:**

- First, into the first column in the aligned dataset, enter the formula:

`=IF(ISNA(MATCH(B5,$C$5:$C$12,0)),"",INDEX($C$5:$C$12,MATCH(B5,$C$5:$C$12,0))`

- Then, use the
**Fill Handle**tool down to all the cells we want to apply this formula.

- Now, we can see all the values in the two datasets are aligned with the duplicates values.

**3. Using VBA to Align Two Sets of Data in Excel**

We can use a simple **VBA Code** for aligning the duplicate values in two sets of data. For this, we are using the same dataset which is used for the **IF Function** to align the matching values.

- First, go to the
**Developer Tab**and then select**Visual basic.**This will open the visual basic editor.

- Click the
**Insert**drop-down and select**Module.**This will insert a new module window.

- Or we can open the visual basic editor by
**right-clicking**on the sheet from the sheet bar and then going to**View Code**.

- After that, write down the
**VBA**code here.

**VBA Code:**

```
Sub Findduplicates()
Dim rng As Range
Set rng = Range([B4], Cells(Rows.Count, "B").End(xlUp))
rng.Offset(0, 1).Columns.Insert
With rng.Offset(0, 1)
.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
.Value = .Value
End With
End Sub
```

- Then,
**Run**the code. This will insert a new column and align all the duplicate values in this new column from the two sets of data. And we can see our desired result.

**Conclusion**

By following the steps, we can easily align two sets of data in our workbook. All those methods are simple, fast and reliable. Hope this will help you! If you have any questions, suggestions or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!