How to Align Similar Columns in Excel

How to Align Two Similar Columns in Excel with Empty Cells for Mismatches

To align two similar columns in Excel with empty cells added when data doesn’t match, use formulas with MATCH and INDEX functions or VLOOKUP with partial matching. For text with different references, apply wildcard characters (*). This will create aligned columns with empty cells where there’s no exact match.

Contents

• Main Methods for Aligning Columns
• Formulas for Aligning with Partial Matches
• Using VLOOKUP with Wildcards
• Methods Using VBA
• Practical Examples and Steps
• Alternative Approaches

Main Methods for Aligning Columns

There are several effective approaches for aligning two columns with similar data. The most common ones use Excel’s built-in functions to automatically match values and insert empty cells when no match is found.

MATCH and INDEX Formulas

The primary method involves combining the MATCH and INDEX functions:

=IF(ISNA(MATCH(A2,$C$2:$C$12,0)),"",INDEX($C$2:$C$12,MATCH(A2,$C$2:$C$12,0)))

This formula works as follows:

• MATCH(A2,$C$2:$C$12,0) searches for an exact match of the value from cell A2 in the range C2:C12
• If a match is found, INDEX returns the corresponding value
• If no match is found (ISNA returns TRUE), the formula returns an empty string

Important: Absolute references ($C$2:$C$12) ensure that the search range won’t shift when you copy the formula down.

Creating an Empty Column Between Lists

For visual separation of columns, you can add an empty column:

1. Select the second data column
2. Right-click and choose “Insert”
3. In the dialog box, select “Shift cells right”
4. Click “OK”

Formulas for Aligning with Partial Matches

Since your case involves identical text but with different references, you’ll need to use formulas for partial matching.

Formula with Wildcards

For partial text matching, use wildcard characters *:

=IFERROR(INDEX($C$2:$C$12,MATCH(A2&"*",$C$2:$C$12,0)),"")

This formula searches for values in column C that begin with the text from column A followed by any text. For example, if A2 contains “Product A”, the formula will find “Product A - reference1” and “Product A - reference2”.

Extended Formula for Multiple Columns

If you need to align multiple related columns, use:

=IF(ISNA(MATCH(A2,$C$2:$C$12,0)),"",INDEX($E$2:$E$12,MATCH(A2,$C$2:$C$12,0)))

This formula will align data from column E based on matches in column C with data from column A.

Using VLOOKUP with Wildcards

The VLOOKUP function can also be used to align columns with partial matching.

Basic VLOOKUP Formula with Wildcards

=IFERROR(VLOOKUP($A2&"*",$C$2:$D$12,COLUMN()-COLUMN($A2)+1,0),"")

This formula:

• Adds a wildcard * to the value from A2
• Searches for a partial match in the range C2:D12
• Returns the corresponding value from the appropriate column
• Returns an empty string when no match is found

Example with Multiple Columns

To align data from multiple columns, use:

Methods Using VBA

For more complex automation scenarios, you can use VBA macros.

Simple Macro for Aligning Columns

Sub AlignColumns()
    Dim ws As Worksheet
    Dim rngA As Range, rngC As Range
    Dim i As Long, j As Long
    Dim matchFound As Boolean
    
    Set ws = ActiveSheet
    Set rngA = ws.Range("A2:A100") ' Range of first column
    Set rngC = ws.Range("C2:C100") ' Range of second column
    
    ' Add empty rows to column C to align columns
    For i = rngA.Cells.Count To 1 Step -1
        matchFound = False
        For j = 1 To rngC.Cells.Count
            If InStr(1, rngC.Cells(j, 1).Value, rngA.Cells(i, 1).Value) > 0 Then
                matchFound = True
                Exit For
            End If
        Next j
        
        If Not matchFound Then
            rngC.Cells(i, 1).EntireRow.Insert Shift:=xlDown
        End If
    Next i
End Sub

This macro searches for partial matches between columns and inserts empty rows where there are no matches.

Practical Examples and Steps

Example 1: Aligning Columns with Different References

Source Data:

• Column A: “Product A”, “Product B”, “Product C”
• Column C: “Product A - reference1”, “Product B - reference2”, “Product C - reference3”

Alignment Steps:

1. In an adjacent column (e.g., B2), enter the formula:

   excel

   =IF(ISNA(MATCH(A2,$C$2:$C$4,0)),"",INDEX($C$2:$C$4,MATCH(A2,$C$2:$C$4,0)))
   

2. Drag the fill handle down for all rows

3. The result will be an aligned column with empty cells where there are no matches

Example 2: Aligning with Partial Matching

For text with different references, use:

=IFERROR(INDEX($C$2:$C$100,MATCH(1,ISNUMBER(SEARCH(A2,$C$2:$C$100)),0)),"")

This formula uses the SEARCH function to find partial matches.

Alternative Approaches

Using Power Query

1. Select your data → Data → From Table/Range
2. In Power Query Editor, merge tables with partial match
3. Add a step to handle mismatches
4. Close & Load the data

INDEX and MATCH Formula for Multiple Criteria

=INDEX($E$2:$E$100,MATCH(1,(ISNUMBER(SEARCH(A2,$C$2:$C$100)))*(ISNUMBER(SEARCH(B2,$D$2:$D$100))),0))

This formula searches for matches across multiple columns with partial matching.

Using XLOOKUP Function (for Excel 365)

=IFERROR(XLOOKUP(A2&"*",$C$2:$C$100,$E$2:$E$100,""), "")

The XLOOKUP function provides a simpler syntax structure compared to VLOOKUP.

Sources

1. How to align duplicates or matching values in two columns in Excel?
2. Look up values with VLOOKUP, INDEX, or MATCH
3. Partial match with VLOOKUP - Excel formula
4. How to lookup partial string match in Excel?
5. Align rows from different columns using simple formula in Excel

Conclusion

• The primary method for aligning similar columns is using MATCH and INDEX formulas with error handling
• For partial matching, apply wildcard characters * in lookup formulas
• VBA macros are suitable for automating complex alignment scenarios with large data volumes
• Power Query provides powerful tools for merging data with partial matches
• Experiment with different function combinations to find the optimal solution for your specific data

It’s recommended to start with MATCH and INDEX formulas as they are the most versatile and don’t require additional setup. For very large datasets, consider using Power Query or VBA macros.