Get matching list between two columns

qnguyen

New Member
Joined
Aug 7, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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 ListReference ListFormula Returns
Brand DBrand ABrand D
Brand YBrand BBrand B
Brand BBrand C
Brand ZBrand 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.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
If you have Excel 365.

Book1
ABC
18New ListReference ListFormula Returns
19Brand DBrand ABrand B
20Brand YBrand BBrand D
21Brand BBrand C
22Brand ZBrand D
23Brand E
Sheet8
Cell Formulas
RangeFormula
C19:C20C19=INDEX(B19:B23,AGGREGATE(15,6,MATCH(A19:A22,B19:B23,0),SEQUENCE(COUNT(MATCH(A19:A22,B19:B23,0)))))
Dynamic array formulas.
 
Solution

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
A more dynamic version.

Book1
ABC
1New ListReference ListFormula Returns
2Brand DBrand ABrand B
3Brand YBrand BBrand D
4Brand BBrand CBrand Z
5Brand ZBrand D
6Brand E
7Brand Z
Sheet8
Cell Formulas
RangeFormula
C2:C4C2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1New ListReference ListFormula Returns
2Brand DBrand ABrand DBrand D
3Brand YBrand BBrand BBrand B
4Brand BBrand C 
5Brand ZBrand D 
6Brand E 
7 
8
Master
Cell Formulas
RangeFormula
C2:C3C2=FILTER(A2:A100,COUNTIFS(B2:B100,A2:A100))
D2:D7D2=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

New Member
Joined
Aug 7, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
I also updated my acc details,
Thanks for that. (y)

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
Excel Formula:
=FILTER(A:A,ISNUMBER(MATCH(A:A,B:B,0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top