Super User Asked by user466555 on November 24, 2021
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)
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.
Answered by DiegoAndresJAY on November 24, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP