Get matching list between two columns

[qnguyen]

I have two different brand lists in two different columns, New List and Reference List. I want to make a formula that returns only the matches between two columns, as shown in the table below.

New List	Reference List	Formula Returns
Brand D	Brand A	Brand D
Brand Y	Brand B	Brand B
Brand B	Brand C
Brand Z	Brand D
	Brand E

Both list will change often, so the formula should be able to adapt as the number of columns change. Each column and where the formula returns data is on a different tab within the same document, if that matters (I can take a generic formula and plug in these columns, as long as the formula supports these columns being in different tabs).

I am able to use formulas to check if the New List values are in Reference List. It is making a formula that searches through the New List to automatically return matches in subsequent rows (without empty rows in between) that is giving me trouble. If possible I would prefer to do this with excel formulas and not VBA.

 

[lrobbo314]

If you have Excel 365.

Book1
	A	B	C
18	New List	Reference List	Formula Returns
19	Brand D	Brand A	Brand B
20	Brand Y	Brand B	Brand D
21	Brand B	Brand C
22	Brand Z	Brand D
23		Brand E
Sheet8

Cell Formulas
Range	Formula
C19:C20	C19	=INDEX(B19:B23,AGGREGATE(15,6,MATCH(A19:A22,B19:B23,0),SEQUENCE(COUNT(MATCH(A19:A22,B19:B23,0)))))
Dynamic array formulas.

 

[lrobbo314]

A more dynamic version.

Book1
	A	B	C
1	New List	Reference List	Formula Returns
2	Brand D	Brand A	Brand B
3	Brand Y	Brand B	Brand D
4	Brand B	Brand C	Brand Z
5	Brand Z	Brand D
6		Brand E
7		Brand Z
Sheet8

Cell Formulas
Range	Formula
C2:C4	C2	=LET(nl,OFFSET($A$2,0,0,COUNTA(A:A)-1),rl,OFFSET($B$2,0,0,COUNTA(B:B)-1),ar,MATCH(nl,rl,0),INDEX(rl,AGGREGATE(15,6,ar,SEQUENCE(COUNT(ar)))))
Dynamic array formulas.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

two other options, depending on your version

+Fluff 1.xlsm
	A	B	C	D
1	New List	Reference List	Formula Returns
2	Brand D	Brand A	Brand D	Brand D
3	Brand Y	Brand B	Brand B	Brand B
4	Brand B	Brand C
5	Brand Z	Brand D
6		Brand E
7
8
Master

Cell Formulas
Range	Formula
C2:C3	C2	=FILTER(A2:A100,COUNTIFS(B2:B100,A2:A100))
D2:D7	D2	=IFERROR(INDEX(A2:A100,AGGREGATE(15,6,(ROW(A2:A100)-ROW(A2)+1)/(ISNUMBER(MATCH(A2:A100,B2:B100))),ROWS(D$2:D2))),"")
Dynamic array formulas.

 

[qnguyen]

Thank you both for your replies!

I ended up using the first solution posted since I managed to adjust it to use whole columns, like A:A and B:B.

I really like the simplicity of =FILTER(A2:A100,COUNTIFS(B2:B100,A2:A100)) but it seems to not work if I do A:A and B:B. What a cool formula though, I will need to look into it!

The other ones are way over my head

I also updated my acc details, thank you for the suggestion.

 

[Fluff]

I also updated my acc details,

Thanks for that.

You should try to avoid using whole column references as they can slow your workbook down significantly.
But if you want to do that you could use

=FILTER(A:A,ISNUMBER(MATCH(A:A,B:B,0)))