# Align rows from different columns (which contain some cells that have identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table 2 with 1 or even creating a 3rd Table as long as it aligns with table 1. How can I achieve this?

I have tried the following formula but I have to repeat it across cells to create a row and drag-down (copy downwards):

=IFERROR(VLOOKUP(\$A1,Array,1,FALSE)," ")

=IFERROR(VLOOKUP(\$A1,Array,2,FALSE)," ")

=IFERROR(VLOOKUP(\$A1,Array,3,FALSE)," ") [this would show the 3rd cell in a row]

I am guessing there could be a more easier way.

Example: Trying to transform the tables below,

``````{TABLE 1}                   {TABLE 2}
Z   4   1                   F   4   8
A   4   9                   B   7   2
B   3   7                   A   3   9
L   3   9                   G   3   5
D   4   2                   M   7   2
F   4   2                   D   1   3
``````

into the tables below

``````{TABLE 1}                   {TABLE 3}
Z   4   1                               (gap is left for unmatched rows)
A   4   9                   A   3   5   (matched and aligned)
B   3   7                   B   6   2   (matched and aligned)
L   3   9                               (gap is left for unmatched rows)
D   4   2                   D   1   3   (matched and aligned)
F   4   2       and         F   0   8   (matched and aligned)

G   3   5   (unmatched and separated)
M   7   2   (unmatched and separated)
``````

## 1 Answer

How about INDEX and MATCH?

You can do a MATCH in one column and use that in the INDEX function for the other columns in the same row. That way you only lookup the row once.

You would basically have two tables on top of each other. The top one showing values aligned with table 1 that have a match in table 2. The bottom one showing values in table 2 that aren't in table 1. There would be odd spacing for the bottom table (similar to the spacing in the top one).

You could use VBA to clean up these blank rows in the bottom table pretty easily.

• Thanks for the response DiegoAndresJAY! I'm going to try this and post back asap. – user466555 Aug 7 '15 at 12:42