Returning all instances of list of values

[staregirl]

Good morning!

I need some help breaking something into steps.

I am fine with the logic of both VLOOKUP and INDEX MATCH, but:

Simplified Example:

I have a worksheet with 100k lines. Each line contains a client and details of a document that belongs to the client.

We have 1000 clients with 100 documents each, but I am only interested in finding the documents belonging to 500 of them.

There is a unique identifier for the Client, and I have my list of 500, but each client owns multiple documents.

How can I build a formula that will look at my list of 500 clients and then return the entire row for ALL rows in my worksheet that contain ANY of the desired Client Refs?

(Using my example, I would have 100 documents returned for CN1, then another 100 for client CN2 and so on. The order they are returned doesn't matter)

I've tried Google, and found I can use INDEX MATCH and an array to achieve what I want for ONE client with multiple entries, but how do I do all 500 at once?

Thanks for any help!

 

[Chihiro]

This sort of operation is best done using PowerQuery or other SQL based tools.

What version of Excel do you have and what license SKU do you have?

Formula approach is possible. But it will be resource intensive and will take long time to complete.

To get better help, I'd recommend uploading sample data with enough data to represent your issue (mirroring your actual set up).

 

[staregirl]

Aha! Yes, I have the full 365 Business Gold with bells on version, so I have PowerQuery, but I've not investigated it much.

Attached is a sample of the data. There are 29 clients in the sample, but let's say I only want to extract the rows containing the 10 client numbers I have isolated in Column J.

I'll have a look at PowerQuery and see what I can work out, but I appreciate any and all help. Thank you!

 

[Chihiro]

Here you go.

First convert both lists into Excel Table format.

Using PowerQuery, load both table as connection only.

Then edit Table2 (list of Client Ref) and merge with Left Outer Join using Client Ref as key field.


Expand the NewColumn (uncheck "Use original column....").


Load resulting table to new sheet.

See attached.

 

[staregirl]

A thousand likes! And simple - I must grab this Power BI stuff with both hands, clearly! Thank you so much