Forum Discussion
Align duplicates from two columns to same rows in Excel + additional rows left & right
Hello,
I would like to know how to align duplicates from two columns to same rows in Excel + additional rows left & right
See Excel File attached or here description of the example:
Data (Keywords) on left site "C" + additional information "A" + "B" aligns with
Data (Keywords) on right site "D" + additional information "E" + "F"
E.g.
| Column A | Column B | Column C | |
Row 1
| Search Volume | KW Difficulty Score | Keywords KWTool |
| Row 4 | 480 | 71.15 | constant contact alternatives |
aligns with
| Column D | Column E | ColumnF | |
| Row 1 | Keywords Ahrefs | KW Difficulty Score | Search Volume |
Row 17 | constant contact alternatives | 31 | 250 |
So it looks like this
| Search Volume (Average) | KW Difficulty Score | Keywords KWTool | Keywords Ahrefs | KW Difficulty Score | Search Volume |
| 480 | 71.15 | constant contact alternatives | constant contact alternatives | 31 | 250 |
Any formula that works?
Cheers
TJ
troyyy Ah, yeah! Forgot about those. Due to the limitations of VLOOKUP and since you don't have access to the newer functions, the easiest would be to place the first keyword column to the left of all others. Done that and added the correct VLOOKUP formulae. See attached file ending with "_rev.xlsx".
If, on the other hand you can not move the columns around, you can make VLOOKUP find a match towards towards the left of the "lookup column", using the technique describe in the attached link.
https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose
Alternatively, use a combination of INDEX and MATCH. Both methods are demonstrated in the file ending with "......_rev2.xlsx".
9 Replies
troyyy If you are a MS365 subscriber, you could use FILTER as demonstrated towards the far right, in the attached file.
If FILTER is not supported in your Excel version you can revert to VLOOKUP. Come back here if you need more help.
Hey Riny_van_Eekelen
unfortunately I'm not a MS365 user. I've been looking into VLOOKUP trying to understand it. But I don't understand that formula or at least it doesn't work
I tried to do it - see the excel file.
But for whatever reason it doesn't work.
Any thoughts on that?
Cheers
Torge
troyyy Not sure, but have a look at the attached. With VLOOKUP, the lookup value should be in the first column of the lookup range. Done that in column G. The #NA errors indicate that the value in D is not found in C. Does this help?
