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,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)

How about INDEX and MATCH?

Tutorial: http://www.mrexcel.com/articles/excel-vlookup-index-match.php

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.