Help joining tables with one key and multiple repeating values on the other

Occasional Visitor

Hello! I'm struggling to combine two sheets using the column of one as a key, and XLOOKUP is no help since the second sheet has multiple rows that match each of the key rows in the first.

 

Sheet A has 65 unique IDs and no other data. Sheet B has 20,000+ rows which contain 1000s of IDs and the information I'm looking to get for each of my 65 IDs. Each of the 65 IDs in Sheet B has 1+ rows of unique data that I want to keep.

 

Basically the need to isolate 65 IDs in this 1000+ ID Sheet B to then work with the remaining data and I haven't been able to find a clean way to do this that doesn't force me to do it with one ID at a time.

 

Any help would be appreciated, thank you!

 

sheet a.pngsheet b.pngideal.png

1 Reply

@votrava 

You can try Power Query. The worksheets "Sheet A" and "Sheet B" in the attached file contain blue dynamic tables where you can add data. The green table in the third worksheet is the result table. In order to refresh the query (after adding data) you can click in any cell of the green table and right-click with the mouse. Then select refresh.

ID.JPG