3 Easy Ways to Align Two Sets of Data in Excel

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.


๐Ÿ“ 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.

Dataset - align two sets of data in excel


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.

VLOOKUP function - align two sets of data in excel

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

IF function - align two sets of data in excel

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

VBA method - align two sets of data in excel

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

Insert row - align two sets of data in excel


๐Ÿ“„ 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.

(Visited 31 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo