Organizing data using various techniques is quite common in Excel. Records are arranged in a tabular form here. After entering data, you should align two sets of data in Excel. These two sets should be interconnected. You can edit a few of the records. But in cases of an extensive dataset, you should consider Excel formulas or shortcut techniques to align two sets of data in Excel. This will help you in three ways to guide you in the alignment. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.

**Contents**hide

## ๐ Download Excel File

Download the Excel file below.

## Learn to Align Two Sets of Data in Excel with These 3 Approaches

The article is going to introduce three approaches in Excel to align two sets of data. These methods will help you understand the uses elaborately. The following discussion will cover uses of the **VLOOKUP** function, **IF** function, and finally a **VBA** code. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.

**approach**

### 1. Applying VLOOKUP Function

**The VLOOKUP function** is a finder function that can work across sheets. You can widely use this function to align two sets of data in Excel. In order to remove the error, we would like to utilize **the IFERROR function**.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, select cell ID
**H6**. - Secondly, enter the following formula on the cell.

**=IFERROR(VLOOKUP($B6,$E:$F,1,0),โโ)**

- Thirdly, press the
**Enter**key.

- Using the
**Fill Handle**icon, copy the formula down all the cells of the column.

- Now, copy the same formula for the next column. Use โ
**2**โ instead of โ**1**โ this time as you need the 2nd column of the table this time.

**=IFERROR(VLOOKUP($B6,$E:$F,2,0),โโ)**

- In the same way, copy the formula down to the remaining cells using the
**Fill Handle**icon. You should get an interface like the following image.

**๐จ Formula Breakdown**

**IFERROR(VLOOKUP($B6,$E:$F,2,0),โโ)**

๐ย Here, **VLOOKUP($B6,$E:$F,2,0)** looks for the data in cell **B6** from the table array of columns **E** to **F**. The return value will come from 1st column of the array table.

๐ย Also, **IFERROR(VLOOKUP($B6,$E:$F,2,0),โโ)** shows the data as blank if it finds any error in the output.

Finally, โ**Lisa**โ is shown as the output from the formula.

**approach**

### 2. Utilizing IF Function

**The IF function** uses frequently in Excel. It will allow you to make reasonable comparisons between data and criteria. The **IF** function combined with **ISNA**, **MATCH**, and **INDEX** functions can help you align two of your data sets in Excel.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- Initially, select cell ID
**H6**. - Next, enter the following formula on the cell.

**=IF(ISNA(MATCH(B6,E$6:E$12,0)),โโ,INDEX(E$6:E$12,MATCH(B6,E$6:E$12,0)))**

- After that, press the
**Enter**key.

- Now, using
**Fill Handle**icon, copy the formula down all cells of the table. For the score column, you will notice a different set of output as the formula is matching with the score column.

**๐จ Formula Breakdown**

**IF(ISNA(MATCH(B6,E$6:E$12,0)),โโ,INDEX(E$6:E$12,MATCH(B6,E$6:E$12,0)))**

๐ย Here, **MATCH(B6,E$6:E$12,0)** searched **B6** amidst range **E6** to **E12**. It returns search value.

๐ย Also, **INDEX(E$6:E$12,MATCH(B6,E$6:E$12,0))** returns the value found through its positioning.

๐ย Again, **ISNA** is for values for error cases. You can use it to avoid getting a โ**N/A**โ warning within your table.

๐ย Finally, **IF** gets the condition and data range to return the data as per your requirement.

Therefore, you get the value โ**Lisa**โ from the formula.

**approach**

### 3. Employing VBA Code

Using **VBA (Visual Basic for Applications)**, you can employ **Macro code** to apply on your worksheet. Moreover, it is also the only method that will create a new column for your common data automatically. With the proper instruction, you can make your work easier. Following is an example to find out common student names.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- In the beginning, press
**Alt+F11**to open**Visual Basic Application (VBA)**. - Now, select the
**Module**option from**Insert**tab. You can also press**Alt+I+L**for the action. - Write the following code on the
**Module**box.

```
Sub Align_Two_Sets_of_Data()
Dim rngX As Range
Set rngX = Range([B4], Cells(Rows.Count, "B").End(xlUp))
rngX.Offset(0, 1).Columns.Insert
With rngX.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
```

- You should see the window like the image below.

- Now, go back to the workbook window.
- Next, press
**Alt+F8**or choose the**Macros**option from**the Developer tab**. - In this stage, the
**Macro**window will pop up to confirm the macro. Click on**Run**.

- Another new column โ
**Aligned**โ has been generated with all the common data from both the โ**Student List**โ. You should get an interface like the following picture.

## How to Insert Multiple Rows in Excel Between Data Automatically

Inserting rows is a regular task in data recording through Microsoft Excel. Apart from inserting new rows using the **Ribbon** or the **Context Menu**, you can use keyboard shortcuts as well.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- In the beginning, click on
**row header 7**to add a new row between the**6th**and**7th**rows.

- Now, press
**Alt+I**from your keyboard. - Then, press
**Alt+R**ย You will find a new row automatically. - Keep repeating the shortcut for every new row.

## ๐ Important Notes

๐๏ธย The **VBA** method might interrupt your format on cells. It might occur during the automatic insert of a new column.

๐๏ธย You need to use the **VLOOKUP** and **IF** functions carefully. The absolute values used here might need to change according to your data requirements.

## ๐ Takeaways from This Article

The article lets the readers understand the variety of options available to align two sets of data in Excel.

๐ย Primarily, you can use the **VLOOKUP** function to align two sets of data. It can even work across sheets.

๐ย You can combine the **IF** function with **ISNA**, **INDEX**, and **MATCH** functions to do the alignment. Although the formula requires individual attention, it is an effective method.

๐ย With the help of **VBA** a certain **Macro** can be run on the cell ranges. This method aligns two sets of data in Excel.

## Conclusion

To align two sets of data in Excel, three methods are useful. You can use the **VLOOKUP **function for the alignment formula. You can even combine the **IF** function with **ISNA**, **INDEX**, and **MATCH** functions. Eventually, a **Macro Code** can help you with it as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.