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 the ways shown below.

Below, for your better understanding, we have added an overview image.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**6 Suitable Examples 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.

For the following methods, we’re going to use two sets of data. The** first** dataset contains some product names and the prices of the products.

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

**1. Using VLOOKUP Function to Line up Two Sets of Data 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. Now, we are going to align those two data and merge them in a new set of data named **Aligned Dataset**.

- 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**and 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)`

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

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

Here, we have added **the IFERROR function **with the previous formula.

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:

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

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

`=IFERROR(VLOOKUP(B5,$E:$G,3,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. Merging IF, ISNA, MATCH & INDEX Functions for Aligning Duplicate Values in Two Sets of Data **

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 that is in **Product List 2**. Some products are similar, so we are going to align those products.

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

**Formula Breakdown**

**MATCH(B5,$C$5:$C$12,0)—>****The MATCH function**will find the exact match of**B5**cell value within the**$C$5:$C$12**array.**Output: 2**.

- Now,
**INDEX($C$5:$C$12,2)—>**Here**the INDEX function**will return the**2nd**row from the given array**$C$5:$C$12**.**Output: “Shampoo”**.

- Similarly,
**MATCH(B5,$C$5:$C$12,0)—>**turns**2**. - So,
**IF(ISNA(2),””, “Shampoo”)—>**this will be the final expression of the formula.**The ISNA function**will examine whether the cell value is error or valid. As**2**is a**valid**number so**ISNA function**will give**FALSE**so**the IF function**will show “**Shampoo**” as output, on the other hand it will show a void space.

### 3. Combining IF, ISNA & VLOOKUP Functions to Align Duplicate Values

Here, we can use another combination of some Excel functions. They are the **IF**, **ISNA,** and **VLOOKUP** functions. With this formula, you can align the duplicate values from** two sets** of data in Excel.

- Firstly, write the following formula in the
**E5**cell.

`=IF(ISNA(VLOOKUP(B5,$C$5:$C$12,1,0)),"",VLOOKUP(B5,$C$5:$C$12,1,0))`

- Secondly, press
**ENTER**.

**Formula Breakdown**

**VLOOKUP(B5,$C$5:$C$12,1,0)**—> will return “**Shampoo**” where this**VLOOKUP**function is looking up for the**B5**cell value, within**$C$5:$C$12**this array. And**1**is the column index number.- Then,
**ISNA(“Shampoo”)**—> is the logical test for**the IF function**. Here,**the ISNA function**will check whether the value is an error or not. So, this will give**FALSE**as output. - Then,
**the IF function**will return a**void**space when the logical test is**TRUE**otherwise will operate**VLOOKUP(B5,$C$5:$C$12,1,0)**this operation. **VLOOKUP(B5,$C$5:$C$12,1,0)**this is a similar operation to the**first**one. Where this**VLOOKUP**function is looking up for the**B5**cell value, within**$C$5:$C$12**this array. And**1**is the column index number.**0**is for the exact match.

- Then, drag the
**Fill Handle**icon to paste the used formula respectively to the other cells of the column.

Lastly, you will get all the Aligned datasets that are duplicated in both datasets.

### 4. Merging IFERROR, VLOOKUP & COLUMN Functions to Extract Similar Values from Two Sets of Data

Again, we can use a new combination of some Excel functions. They are the **IFERROR**, **VLOOKUP,** and **COLUMN** functions. Similarly, with this formula, you can line up the duplicate values from** two sets** of data.

- Firstly, write the following formula in the
**E5**cell.

`=IFERROR(VLOOKUP(B5,$C$5:$C$12,COLUMN()-COLUMN($E5)+1,0),"")`

- Secondly, press
**ENTER**.

**Formula Breakdown**

- Here,
**COLUMN()-COLUMN($E5)+1**is the number of the column index for**the VLOOKUP function**.**The COLUMN function**gives the numerical representation of a given column. As the formula is used in the E column,**COLUMN()**becomes**{5}**. Also,**COLUMN($E5)**turns**{5}**. Ultimately,**COLUMN()-COLUMN($E5)+1**will be**5-5+1**which is equal to**1**. - So, the formula will be as
**VLOOKUP(B5,$C$5:$C$12,{1},0)**. In this term,**the VLOOKUP function**will search for the value situated in the**B5**cell, within the**$C$5:$C$12**array. Then,**1**is the column index number and**0**denotes exact matching. - Lastly,
**the IFERROR function**will check whether the value is an error or not.

- Now, drag the
**Fill Handle**icon to paste the used formula respectively to the other cells of the column.

Lastly, you will get all the Aligned datasets that are duplicated in both datasets.

**5. Use of Consolidate Feature for Summing Up Values within Two Sets of Data in Excel**

Here, we will use the **Consolidate** feature under the **Data** tab to align data along with doing some calculations like sum, average, maximum, minimum, etc. within the data sets. Now, let’s talk about how you can use this **Consolidate** feature in Excel.

You have to keep a common column header along with numerical values within those **two** datasets. The** Consolidate **feature will use them to do the calculation.

- Now, select the
**B14**cell. - Then, from the
**Data**tab >> click on the**Consolidate**feature, which belongs to**Data Tools**.

At this time, a new dialog box named** Consolidate** will appear.

- First, choose the function. Here, we have chosen
**the SUM function**. So, the**Consolidate**feature will add the prices for the same product. Say, in the**first**dataset, there is**Sampoo**in the**B5**cell, which price is**$760**. Now, this**Consolidate**feature will search for**Sampoo**in the**second**dataset. If it gets**one**, then it will add that price to the**$760**. So, here the output will be**$760+$960**for the product**Shampoo**.

Below, we have attached the final results.

If you want, then you can change the column header to a suitable **one**. For like, we have changed the column header to **Sold Product,** and **Total Sales**.

**6. Employing VBA Code 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 Align_duplicates()
Dim my_rnge As Range
Set my_rnge = Range([B4], Cells(Rows.Count, "B").End(xlUp))
my_rnge.Offset(0, 1).Columns.Insert
With my_rnge.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
```

**Code Breakdown**

- Here, we have created a
**Sub Procedure**named.*Align_duplicates* - Next, declare a variable
**my_rnge**as**Range.** - After that, we used a
**formula**to align the duplicate values from two sets of data.

- Now,
**Save**the code then go back to**Excel File.** - From the
**Developer**tab >> select**Macros****.**

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

**Practice Section**

Now, you can practice the explained method by yourself.

**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!