Compare two tables, find matches, and extract rows based on matches.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

If you are willing to work with Power Query then the following table can be achieved

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Skol-enhetskod	Skolkommun	Table2.Skol-enhetskod	Table2.Skolkommun
2	76026322	Kiruna	76026322	Kiruna
3	44673074	Stockholm	44673074	Stockholm
4	99648792	Stockholm	99648792	Stockholm
5	22335099	Knivsta	22335099	Knivsta
6	47684564	Örebro	47684564	Örebro
7	15561397	Svedala	15561397	Svedala
8	71387206	Ale	71387206	Ale
9	41408780	Nacka	41408780	Nacka
10	10715898	Österåker	10715898	Österåker
11			37796564	Stockholm
12	48184058	Vallentuna	48184058	Vallentuna
13	28145722	Vallentuna	28145722	Vallentuna
14	59092675	Timrå	59092675	Timrå
15	90711097	Stockholm	90711097	Stockholm
16	84230115	Hultsfred	84230115	Hultsfred
17	27323097	Solna	27323097	Solna
18	85416841	Jönköping	85416841	Jönköping
19	92304123	Ovanåker	92304123	Ovanåker
20	65024773	Bollnäs	65024773	Bollnäs
21	82838118	Stockholm	82838118	Stockholm
22	60217642	Skurup	60217642	Skurup
23	45270097	Vara	45270097	Vara
24	56528690	Åtvidaberg	56528690	Åtvidaberg
25	51403955	Lerum

Sheet: Merge1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Specifically, I loaded each table into the PQ editor and the did a merge(Full outer Join) to get the results.

Here is the file for you to review.

Pl see file.
In G3, H3, I3 copied down

=IFERROR(INDEX(D$3:D$25,AGGREGATE(15,6,ROW($D$3:$D$25)/(ISNUMBER(MATCH($D$3:$D$25,$A$3:$A$25,0))),ROWS($G$3:$G3))-ROW($D$2)),"")

=IF($G3="","",INDEX($B$3:$B$25,MATCH($G3,$A$3:$A$25,0)))

=IF($G3="","",INDEX($E$3:$E$25,MATCH($G3,$D$3:$D$25,0)))

create New Table 1, by putting this formula in cell G3, then drag across to F3, then drag G3:F3 down for as many rows as you like:

Please Login or Register  to view this content.

create New Table 2, by putting this formula in cell J3, then drag across to K3, then drag J3:K3 down for as many rows as you like:

Please Login or Register  to view this content.

bonus conditional formatting...

green table 1 range $A$3:$B$25

Please Login or Register  to view this content.

red table 1 range $A$3:$B$25

Please Login or Register  to view this content.

green table 2 range $D$3:$E$25

Please Login or Register  to view this content.

red table 2 range $D$3:$E$25

Please Login or Register  to view this content.

sample file attached